To call some sql code to reset values of local variables

Question

I am using Sql Server 2008. In my sql file, I have defined 5 local variables (all of them int) and set them all to 0. I then do some operations (not in a loop). After each operation, I need to reset the local variables' values to 0. Right now, I am manually setting all the variables to 0 after each operation. Is there a better way to reset them all to 0 each time, maybe by calling some function rather than maunally doing it?

DECLARE @int1 int
DECLARE @int2 int
DECLARE @int3 int
DECLARE @int4 int
DECLARE @int5 int

SET @int1 = 0
SET @int2 = 0
SET @int3 = 0
SET @int4 = 0
SET @int4 = 0

-- Operation 1
SELECT * FROM Orders

-- Reset values (Number of times the reset code written: 1)

SET @int1 = 0
SET @int2 = 0
SET @int3 = 0
SET @int4 = 0
SET @int4 = 0
-------------------- END OF OPERATION 1 and RESETTING VARIABLES ---------------

-- Operation 2

SELECT * FROM Lines

-- Reset values (Number of times the reset code written: 2)

SET @int1 = 0
SET @int2 = 0
SET @int3 = 0
SET @int4 = 0
SET @int4 = 0
-------------------- END OF OPERATION 2 and RESETTING VARIABLES ---------------

-- Operation 3

SELECT * FROM Customers

-- Reset values (Number of times the reset code written: 3)

SET @int1 = 0
SET @int2 = 0
SET @int3 = 0
SET @int4 = 0
SET @int4 = 0
-------------------- END OF OPERATION 3 and RESETTING VARIABLES ---------------

-- Operation 4

SELECT * FROM Address

-- Reset values (Number of times the reset code written: 4)

SET @int1 = 0
SET @int2 = 0
SET @int3 = 0
SET @int4 = 0
SET @int4 = 0
-------------------- END OF OPERATION 4 and RESETTING VARIABLES ---------------

-- Operation 5

SELECT * FROM Accounts

-- Reset values (Number of times the reset code written: 5)

SET @int1 = 0
SET @int2 = 0
SET @int3 = 0
SET @int4 = 0
SET @int4 = 0
-------------------- END OF OPERATION 5 and RESETTING VARIABLES ---------------

As it is clearly evident, resetting the local variables has been written 5 times (identified by "Number of times the reset code written: #"). Is there a better way to call a function which does the reset so that I only need to call a function, for example ResetVaraibles() that will have the code of resetting the local variables. Is it more clear now?

have you considered posting actual code rather than a poor description of it? — Mitch Wheat, May 03 '12 at 13:48
the fact you want to do that suggests you might be approaching the real problem incorrectly — Mitch Wheat, May 03 '12 at 14:08
The real problem is that I do not want to write resetting code N number of times. I am in search of reusable code that can help me do that by simply calling that reusable code N number of times. Typically, this can be easily achieved by calling a function in any probramming language. — user1274655, May 03 '12 at 14:12
That's the problem: you appear to be thinking in procederal terms. Perhaps a set based solution more applicable. — Mitch Wheat, May 03 '12 at 14:13
So the whatever code I have (resetting it 5 times), is the solution to this? — user1274655, May 03 '12 at 14:16

score 0 · Answer 1 · answered May 03 '12 at 17:10

You could have stored procedure with 5 out parameters.

create procedure ResetTo0
  @int1 int out,
  @int2 int out,
  @int3 int out,
  @int4 int out,
  @int5 int out
as

set @int1 = 0
set @int2 = 0
set @int3 = 0
set @int4 = 0
set @int5 = 0

And it would be called like this.

exec ResetTo0 @int1 out, @int2 out, @int3 out, @int4 out, @int5 out

But I don't think you gain much (if anything) compared to using select to reset the values.

select @int1 = 0, @int2 = 0, @int3 = 0, @int4 = 0, @int5 = 0

score 0 · Answer 2 · edited May 02 '16 at 10:05

0

Way 1 : Disconnect and Connect to SQL Server.

Way 2 : Ctrl-A Ctrl-C Ctrl-N Ctrl-V Copy code to new window.

edited May 02 '16 at 10:05

Thomas G

9,886
7
28
41

answered May 02 '16 at 06:47

Ayhan Sarıtaş

89
3

To call some sql code to reset values of local variables

2 Answers2