HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to re-declare T-SQL variables if it already exist?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlexistdeclarealreadyvariableshow

Problem

Background:

I'm building a SQL command to be executed on the server which involves a lot of accessing store procedure and declaring variables which are used as input to other stored procedures. This is all generated on the client side using C# and send as a big package. I have to do it this way since the ping times are very long since the client will be located very remotely with bad connections and I don't want to send many packages and starting building up a backlog due long response times since.

Currently no more than three client can work in parallel or they start to build up a backlog since the transfer speed of one transaction with about 200 store procedures takes about 35 seconds.

Question:

How can I declare a variable which I don't now if it already exists?
I could use a dictionary on the C# side but that sounds like the wrong way to do it to me. I want a declare which accepts re-declaring of that variable if it did exist.

Solution

You cannot redeclare variables, nor can you test to see if they have been declared (at least not that I have ever been able to find). BUT, you shouldn't need to do this anyway. If you know the variable names ahead of time, just declare them all at the beginning of the process. Then, use them throughout the script.

For example:

--------------------------------
-- BEGIN script header
--------------------------------
DECLARE @SomeVariable1 INT,
@SomeVariable2 VARCHAR(50),
....;
SomeVariableN DATETIME;
--------------------------------
-- END script header
--------------------------------


And then add 1 or more script blocks that use these variables:

---- Script Block 1 ---
EXEC dbo.MyProc @InputParam = 1, @OutputParam = @SomeVariable1 OUTPUT;
-----------------------

---- Script Block 2 ---
EXEC dbo.AnotherProc @InputParam = @SomeVariable1;
-----------------------

---- Script Block 3 ---
SET @SomeVariable2 = NULL; -- reset value if you don't want to carry over prior value
..do something here...
-----------------------

Context

StackExchange Database Administrators Q#96797, answer score: 8

Revisions (0)

No revisions yet.