Multiple Sets of Parameters Into Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Sets of Parameters Into Stored Proc

Author

Topic

axisperfect
Starting Member

21 Posts

Posted - 2004-10-27 : 02:22:18

Here's our process flow so far.
Setting: Database A (DB_A) and Database B (DB_B) reside on the same machine.
1. DB_A: Select ParamA,ParamB,ParamC for ID = @ID (only one row will be returned)
2. DB_A: Pass ParamA,ParamB,ParamC into DB_B stored procedure.
3. DB_B stored proc: Store parameters into a log, make some nifty derivations using derived tables and inner joins, and return a "calculated" value.
4. DB_A: Calculated value retrieved and stored.

Now the problem is that, due to changes by the POWERS-THAT-BE, when selecting ParamA,ParamB,ParamC for ID = @ID, more than one row may be generated. The quick and dirty fix would be to loop step 2-4 for each row returned, but I was wondering if there was a better way? (Looping could also be problematic if something goes wrong mid-way)

Some ideas that have been tossed around:
- Pass a "set" of parameters into a stored procedure (e.g. # rows of ParamA,ParamB,ParamC); DB_B stored procedure would then insert those multiple rows into log with a common ID, do its derivations in one go and return all the calculated values once.
- Instead of passing a 'set', allow DB_B and DB_A to access each other directly; for instance, (pseudo-code)

insert into DB_B_Log_table SELECT ParamA,ParamB,ParamC Where ID = @ID

and THEN do the calculations in DB_B, and again, return the values in the same way

INSERT INTO DB_A_RESULT SELECT results FROM DB_B_Calculated where ID = @Id

But I'm wary of option 2 'cause it seems bad to allow databases to access each other so intergrally. I'd love option 1, but as far as I know -- there's no way, is there?

Help?

Kristen
Test

22859 Posts

Posted - 2004-10-27 : 04:16:19

Pass a temporary table to DB_B_SProc?


USE pubs
GO
CREATE PROCEDURE dbo.SProc_B
AS
SET NOCOUNT ON
SELECT [DEBUG] = 'SProc_B Start'
SELECT *
FROM #TEMP
SELECT [DEBUG] = 'SProc_B End'
GO

USE Northwind
GO
CREATE PROCEDURE dbo.SProc_A
AS
SET NOCOUNT ON
SELECT [DEBUG] = 'SProc_A Start'
SELECT CategoryID, CategoryName 
INTO #TEMP
FROM dbo.Categories

SELECT [DEBUG] = 'SProc_A Call SProc_B'
EXEC pubs.dbo.SProc_B
SELECT [DEBUG] = 'SProc_A End'
GO

EXEC Northwind.dbo.SProc_A
GO

USE pubs
GO
DROP PROCEDURE dbo.SProc_B
GO
USE Northwind
GO
DROP PROCEDURE dbo.SProc_A
GO

gives:


DEBUG         
------------- 
SProc_A Start

DEBUG                
-------------------- 
SProc_A Call SProc_B

DEBUG         
------------- 
SProc_B Start

CategoryID  CategoryName    
----------- --------------- 
1           Beverages
2           Condiments
3           Confections
4           Dairy Products
5           Grains/Cereals
6           Meat/Poultry
7           Produce
8           Seafood

DEBUG       
----------- 
SProc_B End

DEBUG       
----------- 
SProc_A End

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-27 : 04:18:56

>> But I'm wary of option 2 'cause it seems bad to allow databases to access each other so intergrally.
Good instinct.

>> I'd love option 1, but as far as I know -- there's no way, is there?
Instead of passing an id pass a csv string of ids and parse it into a (temp) table in the proc.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources