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?

Solution

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.

Comments

[HABO]

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.

[DeepDiver]

@HABO Uhm...many thanks...but this was not clear...sorry...

[DeepDiver]

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.

[DeepDiver]

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.