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

Question

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.

Answer 1

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...
-----------------------

Answer 2

From reading your question I think you are building the SQL code in your application and then sending it to the server as one long script with multiple statements.

If this is the case you can separate your statements with GO to create batches. The following is valid SQL:

DECLARE @a INT;
SET @a = 1;
SELECT @a;
GO

DECLARE @a INT;
SET @a = 1;
SELECT @a; 

The problem with this is statements can no longer refer to variables declared in separate batches.

I think the real answer is to use stored procedures that are called by your app. This reduces the amount of data sent across the bad link and it ensures the SQL is valid.