Stored Procedure Using Multiple Databases

Sep 14, 2006

Ok, this is kind of an odd problem.  Back in June we were having problems with our call manager software, and they decided to have it just start usinga new database.  Now I'm trying to generate some reports which need to cover both the old call stats and the new, so that means the stored procedure builds a temp table and populates it from both databases.

This works perfectly fine in Management Studio, and when being called from Excel.

However when I try to call it from an ASP.NET web app using SqlCommand.ExecuteReader(), I only get results from the new database!

What on earth could cause that?

View 2 Replies


ADVERTISEMENT

Accessing Multiple Databases From Stored Procedure In SQL 2000

May 16, 2006

Hi ,

I have around 5 databases with same structure used to store data for different locations and services.

I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.

My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.

I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..

--------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE TestDB_access(@dbname varchar(100))
AS

BEGIN

DECLARE @localDB varchar(100)

Set @LocalDB=@dbname

if @LocalDB='XYZ'

EXEC ('USE XYZ ')
elseif @LocalDB='ABC'

EXEC ('USE ABC')


Select * from Mytable

END

---------------------------------------------------------------------------------------------------------------

When I run this from my database , it gives me an error "unable to find table "mytable".

What is the best way to make my queries work with all the databases.

Thanks in advance

Venu Yankarla

View 4 Replies View Related

Transact SQL :: Looping Through Multiple Servers And Databases In Stored Procedure

Aug 27, 2015

I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.

I have table "dbo.instances" with list of instances. 
I have databases from "sys.databases". 

How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.

View 3 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
 I have 8 questions. 
 Textbox -  Call reference
 Dropdownmenu  - choose Support method
 Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
 

View 9 Replies View Related

Calling Other Databases From Stored Procedure

Jan 8, 2007

Hi,
 I am working with multiple databases on the same server and in a stored procedure I need to be able to call on one of them.
Here is an example of what I am trying to do in this stored procedure:create procedure sp_procedure(@variable int)select anitem from atable where selection = @variable
declare @anothervariable Char(3)
select @anothervariable = item_that_determines_database from atable where selection = @variable
use dbo.@anothervariableselect count(id) from Some_table where selection = @variable
The database is not found using this method and I do need to use it in a stored procedure.  All of the databases being used are (3) letter names in lower case (aaa, bbb, ccc, etc...), the info that @anothervariable pulls from the table is the name of that database but in all caps.  Does this part make a difference?
Also, what method could I use to get the database variable to read from that selected database?
 
Thank-you for your help.
 
Eric

View 3 Replies View Related

Changing Databases In An Stored Procedure

Aug 24, 1999

Hello all,

I have discovered that you cannot use the "USE" command in a stored procedure to change to another database.

Is there any way I can change to another database within a stored procedure besides calling another stored procedure on the database I want?

Thanks,

Nev.

View 1 Replies View Related

Stored Procedure To Loop Through Databases

Mar 16, 2015

We're running SQL Server 2008 and have run into a bit of a situation. We have 5 databases all with the same tables and we are trying to create a query that will loop through the different databases and output the results per company database. I originally did a cursor, but my boss wants the query to be in a more readable format:

His ideal wish would be the query in a stored procedure and the cursor to create the input parameter for the stored procedure for the different databases.I've tried looking through some forums and googling some possibilities but can't seem to make any sense of them.

declare @dbname varchar(100)
,@sql varchar(max)
createtable #TempDBs (
dbname nvarchar(100)
, Orig_Jnl int
, BaseRef int
, Posting_Date date

[code]....

View 7 Replies View Related

Distincts Databases On The Same Stored Procedure

Jun 28, 2006

Hi!

    I need to use two distincts databases on the same stored procedure. One, database1, is where I want to place the procedure, and where the table the procedure populates is (table1). The other, database2, hosts the table (table2) from where I select some data to put into table1 in database1.
    Database1 name is fixed, while database2 name may change. So, I would like to pass database2 name as a parameter to the procedure. In this simple example, it works fine:

use database1
go
create procedure teste @db_name varchar(10) as
exec ('select * from ' + @db_name + '.dbo.table2')

    But the problem is that in my procedure, database2 is used in a cursor, something like this:

create procedure myProcedure @year int, @pDatabase2 varchar(30) AS

declare ...

WHILE ...
BEGIN
  ... 
  declare cursor1 cursor for 
 select column1, column2 from @pDatabase2.dbo.table2
 where ...

  open cursor1
 fetch next from cursor1 into ...

  close cursor1
  deallocate cursor1
   
 -- insert data into table
 INSERT INTO table1
     VALUES...

....

    And I get an error if I try to use the exec! How can I do it? Can anyone help me please?

         Thank you!

View 4 Replies View Related

SQL 2012 :: Compare Two Similar Databases (A And B) Stored Procedure?

Dec 3, 2014

Is there any way to compare two similar databases (A & B) stored procedure. I have to find stored procedure in second database B with respect to the difference.

View 7 Replies View Related

Transact SQL :: Stored Procedure To Determine Which Databases To Restore?

Sep 27, 2015

I have 5 databases that the user will chose which ones to restore. I was thinking the variable with the 5 database names separated by commas. I was thinking about using the CONTAIN function but two of the databases have the same name except for a few letters at the end.

View 3 Replies View Related

Multiple Stored Procedure...or 1 Dynamic Procedure?

Jul 3, 2007

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
Justin

View 2 Replies View Related

Stored Procedure On Multiple DB's

Jun 6, 2002

View 2 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query. 
 

View 3 Replies View Related

Multiple Tasks In Stored Procedure

Feb 5, 2008

I have put together the stored-procedure below to carry out update, delete and insert queries in one visit. The code has been pieced together from the pages listed at the bottom. I pass the procedure three XML strings and after testing it a few times it seems to work fine. I’m fairly new to stored procedures though, so I was hoping someone would answer these questions:
1. Is this an acceptable way to do this? Can you foresee any problems?2. I want to make this an ‘all-or-nothing’ event, i.e. if any part of the procedure fails, it must all fail. How would I achieve that?3. I want to know in my calling code what the result is. I’ve used output parameters before, but I’m unsure how to combine one with 2 above.
Sorry this is a long script, but I’ve removed most of the column names and values to shorten it. Thanks in advance.CREATE PROCEDURE amendPageRecords
@PagesToUpdate xml,
@PagesToDelete xml,
@PagesToInsert xml

AS
BEGIN

-- UPDATING RECORDS ------------------------------------------------------------
DECLARE @UpdateTable TABLE (PageID int, PageType varchar(10))

INSERT INTO @UpdateTable (PageID, PageType)

SELECT PageID = UpdatePages.Item.value('@PageID', 'int'),
PageType = UpdatePages.Item.value('@PageType', 'varchar(10)')
FROM @PagesToUpdate.nodes('Pages/Page') AS UpdatePages(Item)

UPDATE page
SET page.page_type = UP.PageType
FROM page INNER JOIN @UpdateTable UP ON page.page_id = UP.PageID

-- DELETING RECORDS ------------------------------------------------------------
DECLARE @DeleteTable TABLE (PageID int)

INSERT INTO @DeleteTable (PageID)
SELECT PageID = DeletePages.Item.value('@PageID', 'int')
FROM @PagesToDelete.nodes('Pages/Page') AS DeletePages(Item)

DELETE FROM page
FROM page INNER JOIN @DeleteTable DP ON page.page_id = DP.PageID

-- INSERTING RECORDS -----------------------------------------------------------
DECLARE @InsertTable TABLE (SiteID int, PageType varchar(10))

INSERT INTO @InsertTable (SiteID, PageType)

SELECT SiteID = InsertPages.Item.value('@SiteID', 'int'),
PageType = InsertPages.Item.value('@PageType', 'varchar(10)')
FROM @PagesToInsert.nodes('Pages/Page') AS InsertPages(Item)

INSERT INTO page
SELECT SiteID, PageType
FROM @InsertTable

END
GOCode taken from:http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspxhttp://www.eggheadcafe.com/articles/20030627c.asphttp://www.sommarskog.se/arrays-in-sql-2005.html

View 10 Replies View Related

Stored Procedure Using Multiple Selects?

Feb 5, 2008

I need help in figuring out the proper way of writing a stored procedure out correctly to get my desired datasource.  In my ocnIdToRatePlanOptions table, I will recieve a parameter via request.querystring @ocnId to filter out my result set for ocnIdToRatePlan table.  Based on the ocnId filtered I want it to select the corresponding tables too.So, if a querystring is passed that is 3955 in my ocnIdToRatePlanOptions table, I want it to use it to create a select for RatePlan1.   If a querystring is passed that is 1854 in my ocnIdToRatePlanOptions table, I want it to use to create a select for RatePlan2.  Is this possible?  ocnIdToRatePlanOptions Table [otrpoRefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[FKrpoRefId] [int] NOT NULL,1, 3955, 12, 1854, 2RatePlan1 Table[rp1RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL[fee] [decimal](18, 2) NOT NULL1, 3955, 1.002, 2350, 2.00RatePla2 Table[rp2RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[q_0_50] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_0_50]  DEFAULT ((225)),[q_51_100] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_51_100]  DEFAULT ((325)),[q_101_150] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_101_150]  DEFAULT ((345)),[q_151_200] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_151_200]  DEFAULT ((400)),[q_201_250] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_201_250]  DEFAULT ((450)),[q_251_300] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_251_300]  DEFAULT ((500)),[q_301_400] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_300_400]  DEFAULT ((650)),[q_401_600] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_401_600]  DEFAULT ((950)),[q_601] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_601]  DEFAULT ((1.50)) 1,1854, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.502,8140, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.50 

View 12 Replies View Related

Multiple Database Stored Procedure

Apr 18, 2001

I am trying to write a stored procedure that has an inner join between two tables from two different databases on the same sql server.

Something like...

Select * FROM
DB1-table1 INNER JOIN DB2-table1
ON DB1-table1.ID = DB2-table1.ID

yada yada yada.......

Does anyone know how to do this or is it possible? If so, what database should I put the stored procedure in or does it matter?

View 2 Replies View Related

Stored Procedure With Multiple Inputs

Aug 3, 1999

Hello.

I'm trying to write a generic Stored Procedure will select records with the following options.

1. Select XXX from theTable
Where ID = 1

2. Select XXX from theTable
Where ID = 1 or ID = 3 or ID = 4

The issues is that at run time the WHERE CLAUSE COULD CHANGE
(it could have multiple OR-d options)

Is there a way to pass a varChar argument like "Where ID=1 or ID =2"?
Thanks in advance!
-andy

View 2 Replies View Related

Multiple Parameters To A Stored Procedure

Sep 6, 2006

Hi All,

I have a database with very heavy volume of data.

I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.

Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.

Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?

Thanks in advance,

HHA

View 4 Replies View Related

Multiple Keywords In Stored Procedure

Jan 10, 2004

Hi,
I'm trying to move an asp site from an Access DB to SQL Server.
The site features a search system which allows multiple keywords to be used in up to three fields.

The way I used to manage this was very similar to how it is described below (taken from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6701):

"So you're replacing all of the middle spaces with a SQL 'and' statement. In plain English, if your search phrase is "print bug", this now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate the leading and trailing %'s and quotes (this is for Microsoft Access drivers, other drivers may use different wildcards)--so just append this phrase to the "WHERE field LIKE " phrase, and you're in business."

The trouble I'm having is that the search string is sitting in a Stored Procedure. How can I dynamically append to the search string in this case?

If it isn't possible, how can I go about this?

Cheers all.
__________________________________________________ ___
Code:
ASP:
item=request(item)
'****item = Replace(item, " ","%' AND item like '%")**** - needs attention
rs.Open "Exec getlist "& item &"" objConn, 3

SP:
CREATE PROCEDURE getlist
@item nvarchar(255)
SELECT item FROM publications WHERE item LIKE '%' + @item + '%'
GO

View 14 Replies View Related

Multiple Statements In Stored Procedure

Apr 29, 2008

I believe we can you multiple statements in stored procedures?

Suppose I have a stored procedure and I pass parameters to this SP.
What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below:

CREATE sp_temp_proc
@order_id int,
@order_position int,
@temp_order_id OUTPUT
@temp_order_position OUTPUT,

SELECT order_id AS temp_order_id
FROM <TABLE> WHERE order_position < @order_position

@temp_order_id = temp_order_id


UPDATE <TABLE> SET order_position = @order_position WHERE order_id =
@temp_order_id

UPDATE <TABLE> SET order_position = @temp_order_position WHERE order_id = @order_id

View 2 Replies View Related

Using Multiple Transaction In Stored Procedure?

Oct 15, 2014

I want to use 3 transactions in a single stored procedure.First it should update the value and then if the 1st transaction executed successfully then 2nd transaction should start,if 2nd trans executed successfully then 3rd trans should execute.After that only i want to commit all the transactions that is 1,2,3.If any of the trans fails the other shouldn't execute.How do i do this?

USE [recruit]
GO
/****** Object: StoredProcedure [dbo].[Import] Script Date: 10/15/2014 17:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Import]
@CustId int

[code].....

View 2 Replies View Related

Use Multiple Database In 1 Stored Procedure

Oct 22, 2007

how can we use 2 databases in 1 stored procedure.
like for example i want to get records from 1 table and insert it to other tables in another database.
thnx for the help.

View 1 Replies View Related

Stored Procedure For Multiple Inserts

Feb 24, 2008

Hello all,

I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.

Imagine a site that tracks movies. I have 3 tables:

Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)

This is an overly simple example, but it gets to the heart of my problem.

Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance

create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )

that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry

Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.

Thanks.

View 3 Replies View Related

Multiple SQL Statements In A Stored Procedure

Jul 23, 2005

Hi!I got 2 stored procedure, proc1 executes proc2,proc2 does some updates and inserts on different tables ...proc1:ALTER PROCEDUREASexecute proc2SELECT * FROM tblFoo______________________my problem is, that when executing proc1, I receive the message:"THE SP executed successfully, but did not return records!"But I need the resultset from "SELECT * FROM tblFoo" that is executedat the end of proc1.I'm not sure, but I think that I solved a similira problem with "setnocount on", I put it into both SP, but it's still the same ... noresultset ...How can I display "SELECT * FROM tblFoo" within a SP, where SQLstatements are executed before?!Thank you!

View 1 Replies View Related

Multiple Deletes With A Stored Procedure

Jul 20, 2005

Just wondering if this is good form:Alter Procedure "mySPName"@UniqueID intASset nocount onset xact_abort offDELETE FROM tblNameOneWHERE(tblNameOne.UniqueID = @UniqueID)DELETE FROM tblNameTwoWHERE(tblNameTwo.UniqueID = @UniqueID)Is it a good idea to run multiple detele statements within one SP?thanks,lq

View 5 Replies View Related

Help With Multiple Connections In A CLR Stored Procedure

Nov 3, 2005

Here's what I'm trying to accomplish:

View 5 Replies View Related

Multiple Results From One Stored Procedure

May 17, 2007

If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report?



Example stored procedure:



ALTER PROCEDURE [dbo].[proc_DepositsByOfficer]

As

SELECT MASTER_DSC.APP

, MASTER_DSC.BRANCH

, qlkpOfficer.strName

, MASTER_DSC.DSC_OFFICER_49

, qlkpBranchName.strDescrS

, MASTER_DSC.DSC_CUR_BAL_21

, Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL

, Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL

, Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL

, MASTER_DSC.DSC_INT_RATE_45

, Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV

, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD

, Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count

, Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count

, Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count

, qlkpApplicationCode.strDescrS AS strApplCode

FROM

MASTER_DSC

INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference

LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID

LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference

WHERE

MASTER_DSC.DSC_CUR_BAL_21<>0

ORDER BY

MASTER_DSC.BRANCH;



SELECT dbo.fBankName() AS BankName;

View 7 Replies View Related

Multiple Keyword Search In A Stored Procedure

Sep 8, 2006

 Hi Guys, I hope someone here can help me. I am writing a stored procedure that simply searches for a given value across multiple databases on the same server. So far all well and good.Now, the problem is if the user types in more than one word into the search field.I have put a partial section of code here, there is obviously more, but most of it you wouldn't need to see. SELECT @sql = N'SELECT @count = COUNT('+ @dbname +'.dbo.orders.order_id) FROM '+ @dbname +'.dbo.orders '+
N' INNER JOIN '+ @dbname +'.dbo.customer ON '+ @dbname +'.dbo.orders.cust_id = '+ @dbname +'.dbo.customer.cust_id '+
N' WHERE '+ @dbname +'.dbo.customer.forename LIKE ''%'+ @SearchStr + '%'' OR '+ @dbname +'.dbo.customer.Surname LIKE ''%'+ @SearchStr + '%'''

EXEC sp_executesql @sql, N'@count int OUTPUT', @count = @results OUTPUT Now this code works perfectly well if the user only enters one word, however i need to make sure that the Stored procedure will function if the user enters 2 words, such as John Smith. I need the procedure to search the forename for 'john' & 'Smith' and the same for the surname. It should also work if the user type 'John Michael Smith' - if you understand.I am really struggling with this one.Thanks in advance.Darren

View 2 Replies View Related

Multiple Filtering On The Same Field Using A Stored Procedure

Apr 24, 2007

Hello,
I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below:
Input field: Product Description
So if the user enters: "Large Drill"  OR "Drill Large" the same resultset will be returned.
SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill"
I guess there'll need to be a nested Select and loop to parse the space separated input field.
Any pointers would be appreciated.
Thank you
Lee
 
 
 

View 2 Replies View Related

Running Stored Procedure Multiple Times

Jun 25, 2007

I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…

View 7 Replies View Related

Multiple Table Entry In A Stored Procedure

Jul 5, 2007

is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?

View 1 Replies View Related

Handling Multiple Values From A Stored Procedure In ASP.NET.

Jan 21, 2008

Hi all, 
I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have:
ExecuteNonQuery(cmd);
return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower();
Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so:
return (int)ExecuteNonQuery(cmd);
Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help.
Thanks

View 4 Replies View Related

Stored Procedure With Multiple Update Statements

Jan 31, 2008

I have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. "
 The procedure takes in three parameters and retrieves 23 values from the DB to display on my form.
Any ideas?
 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved