Global Variables in Stored Procedures

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

SQL Server Development (2000)

Global Variables in Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-19 : 09:02:33

John writes "Any version of Windows and SQL Server 2000 (no sp).

I have a group of related stored procedures that include some 200+ parameters. It's pretty complicated and, therefore, makes sense to have it broken down into several different stored procedures. Since they need access to these different parameters we're doing a lot of passing of values back and forth which adds a lot of code to the procedures. It would be nice if we could declare variables that would be visible to not only the current procedure but also all procedures called from the procedure.

Making a temporary table appears to take too long. I thought about a cursor with my variables as columns like this:

DECLARE TestCursor SCROLL CURSOR FOR
SELECT @var1 as colname1, @var2 as colname2

but I don't know if I can pass a cursor around between procedures and, more importantly, can't figure out how to update the values.

Any fast way to have access to 200+ parameters in multiple stored procedures without passing them all?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-19 : 10:43:27

I don't know why a temp table would take so long to create, unless you're doing a SELECT...INTO with 1000s of rows. If you don't want to use a temp table, you can have a permanent table with the proper structure, and add a column for SPID, the process ID of the connection. This lets you isolate multiple concurrent calls to the stored procedure; the SP would only put the values for the current SPID.

Something like this:

CREATE PROCEDURE bigOne @param1 int, @param2 varchar(50), @param3 datetime AS
INSERT INTO ParamTable (SPID, param1, param2, param3)
SELECT @@SPID, @param1, @param2, @param3
EXEC sp_step1

CREATE PROCEDURE sp_step1 AS
DECLARE @param1 int
SELECT @param1=param1 FROM ParamTable WHERE SPID=@@SPID

You get the idea. The last procedure should DELETE FROM ParamTable WHERE SPID=@@SPID to ensure that the parameters are properly cleared out...you might even want to issue this before the INSERT statement. Making SPID the primary key (or simply putting a UNIQUE constraint on it) would be a good idea.

Edited by - robvolk on 12/19/2001 10:44:27

jpm
Starting Member

1 Post

Posted - 2001-12-22 : 10:58:42

Thanks for the idea... I really wish TSQL was more powerful of a programming language but since I really hate the idea of passing all those parameters in and out this seems like the only way to go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources