SQL Server : reset variable value

Question

I am writing a query to get data from each DBO on my master.

The query is similar to

DECLARE @variable1 INT
DECLARE @variable2 INT

SET @variable1 = (SELECT column1 
                  FROM database1.dbo.Table_name1 
                  WHERE column1 = value)
SET @variable2 = (SELECT column2 
                  FROM database1.dbo.Table_name2 
                  WHERE column2 = value)

SELECT @variable1/@variable2 AS MyIndex
UNION ALL
SET @variable1 = (SELECT column1 
                  FROM database2.dbo.Table_name1 
                  WHERE column1 = value)
SET @variable2 = (SELECT column2  
                  FROM database2.dbo.Table_name2 
                  WHERE column2 = value)
SELECT @variable1/@variable2 AS MyIndex

However I get the following error

Incorrect syntax near the keyword 'SET'.

on the line where I reset the @variable1 value.

Any help, please?

Tips: Instead of set you can select @variable1= column1 from ...;. This also allows getting multiple values from a single select, e.g. select @a =colA, @B = colC / 42 from ...;. Or set @MyIndex1 = ( select column1 from ... ) / ( select column2 from ... ); to return the result in one swell foop. Thence onward to select ( select ... ) / ( select ... ) as MyIndex union all ( select ... ) / ( select ...); without variables. — HABO
– HABO, Commented May 20, 2017 at 13:59

TT. · Accepted Answer · 2017-05-20 13:40:05Z

1

Reorder the statements like this:

DECLARE @variable1 INT
DECLARE @variable2 INT
DECLARE @variable3 INT
DECLARE @variable4 INT
SET @variable1=(SELECT column1 FROM database1.dbo.Table_name1 WHERE column1=value)
SET @variable2=(SELECT column2 FROM database1.dbo.Table_name2 WHERE column2=value)
SET @variable3=(SELECT column1 FROM database2.dbo.Table_name1 WHERE column1=value)
SET @variable4=(SELECT column2 FROM database2.dbo.Table_name2 WHERE column2=value)
SELECT @variable1/@variable2 AS MyIndex
UNION ALL
SELECT @variable3/@variable4 AS MyIndex

UNION ALL will give the union of two select statements and nothing can go in between. The error message you got is saying that much.

answered May 20, 2017 at 13:40

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DeepDiver Over a year ago

Thanks a lot, that works fine and I learnt something new. The issue I have now is to set 2 variables for each DBO I query. Will have to optimize. My wish was to use variable to avoid repeating queries.

HABO · Accepted Answer · 2017-05-20 15:08:42Z

0

To get the result with two variables you can calculate the two MyIndex values and then union them:

declare @MyIndex1 as Int =
  (SELECT column1 FROM database1.dbo.Table_name1 WHERE column1=value) /
  (SELECT column2 FROM database1.dbo.Table_name2 WHERE column2=value);
declare @MyIndex2 as Int =
  (SELECT column1 FROM database2.dbo.Table_name1 WHERE column1=value) /
  (SELECT column2 FROM database2.dbo.Table_name2 WHERE column2=value);
select @MyIndex1 as MyIndex union all select @MyIndex2;

To get the result without variables:

select (SELECT column1 FROM database1.dbo.Table_name1 WHERE column1=value) /
  (SELECT column2 FROM database1.dbo.Table_name2 WHERE column2=value) as MyIndex
union all
select (SELECT column1 FROM database2.dbo.Table_name1 WHERE column1=value) /
  (SELECT column2 FROM database2.dbo.Table_name2 WHERE column2=value);

Tip: You can set variable values with a select rather than set. This is especially useful when you need more than one value from a single query:

declare @Foo as Int;
declare @Bar as Int;
set @Foo = ( select FooCol from <your query> );
set @Bar = ( select BarCol from <the same query> );

can be written as:

declare @Foo as Int, @Bar as Int;
select @Foo = FooCol, @Bar = BarCol from <your query>;

This has the advantage of only running the query once regardless of the number of columns (or calculations) returned. Since your queries are all different it doesn't help you today.

answered May 20, 2017 at 15:08

HABO

15.9k5 gold badges44 silver badges61 bronze badges

1 Comment

DeepDiver Over a year ago

Many thanks again, I think I need to stick to option 2) and not use variables. I need each query to generate 1 record of results for each DBO. Each field record represents a merge field for an email report. Many thanks, I learn a lot.

Collectives™ on Stack Overflow

SQL Server : reset variable value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related