Femmer Femmer - 6 months ago 5
SQL Question

UNION within multiple if statements

I am essentially trying to write a query that will look at multiple databases and then union the results. As of right now I get all the correct results, without the union.
Here is my current query:

SET NOCOUNT ON
DECLARE @DBName VARCHAR(3)
SET @DBName = 004
DECLARE @join VARCHAR(10)
SET @join = 'UNION ALL'
DECLARE @statement VARCHAR(100)
DECLARE @loopcounter INT
SET @loopcounter = 1
WHILE @loopcounter < 102

BEGIN
IF @DBName IN (004,005,011,012,021,026,027,028,038,039,044,045,050,051,052,053,054,057,060,061,062,063,066,069,073,074,075,076,079,083,084,
085,086,087,088,089,090,091,092,093,094,095,098,099,100,105,106,107,108,109,110,111,112,116,121,122,126,127,128,129,130,131,
132,136,137,138,139,140,141,142,143,144,149,150,151,152,153,154,155,156,159,164,165,166)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
SET @DBName = @DBName + 1
SELECT @statement
UNION ALL
SELECT @join
END
IF @DBName IN (013,015,017,019,022,024,032,046,048,055,058,064,067,077,096,101,103,117,119,145,147,157,160,162)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
SET @DBName = @DBName + 2
SELECT @statement
UNION ALL
SELECT @join
END
IF @DBName IN (029,070,080,113,123,133,167)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
SET @DBName = @DBName + 3
SELECT @statement
UNION ALL
SELECT @join
END
IF @DBName IN (034,040)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
SET @DBName = @DBName + 4
SELECT @statement
UNION ALL
SELECT @join
END
IF @DBName IN (006)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
SET @DBName = @DBName + 5
SELECT @statement
UNION ALL
SELECT @join
END
SET @loopcounter = @loopcounter + 1
END
SELECT 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'


My results as is:

----------------------------------------------------------------------------------------------------
SELECT * FROM a0040040.HTL004.dbo.zz_tb_transactions_base
UNION ALL


----------------------------------------------------------------------------------------------------
SELECT * FROM a0050040.HTL005.dbo.zz_tb_transactions_base
UNION ALL


----------------------------------------------------------------------------------------------------
SELECT * FROM a0060040.HTL006.dbo.zz_tb_transactions_base
UNION ALL


----------------------------------------------------------------------------------------------------
SELECT * FROM a0110040.HTL011.dbo.zz_tb_transactions_base
UNION ALL


----------------------------------------------------------------------------------------------------
SELECT * FROM a0120040.HTL012.dbo.zz_tb_transactions_base
UNION ALL


How can I get my results to union:

SELECT * FROM a0040040.HTL004.dbo.zz_tb_transactions_base
UNION ALL
SELECT * FROM a0050040.HTL005.dbo.zz_tb_transactions_base
UNION ALL
SELECT * FROM a0060040.HTL006.dbo.zz_tb_transactions_base
UNION ALL
SELECT * FROM a0110040.HTL011.dbo.zz_tb_transactions_base
UNION ALL
SELECT * FROM a0120040.HTL012.dbo.zz_tb_transactions_base
UNION ALL


Any ideas?

Answer

Something like this? (a bit simpler then your example, but for the idea :) )

declare @db varchar(3) = '000'
declare @counter int =1;

create table #testt(testc varchar(20))


while @counter <100
begin 
insert into #testt values(@db + CAST( @counter as varchar(20)))
set @counter = @counter +1
END

select * from #testt
drop table #testt

EDIT, based on your comment - the idea still works:

DECLARE @DBName VARCHAR(3)
    SET @DBName = 004
DECLARE @join VARCHAR(10)
    SET @join = 'UNION ALL'
DECLARE @statement VARCHAR(100)
DECLARE @loopcounter INT
    SET @loopcounter = 1


create table #result(query varchar(200))

WHILE @loopcounter < 102

BEGIN
IF @DBName IN (004,005,011,012,021,026,027,028,038,039,044,045,050,051,052,053,054,057,060,061,062,063,066,069,073,074,075,076,079,083,084,
           085,086,087,088,089,090,091,092,093,094,095,098,099,100,105,106,107,108,109,110,111,112,116,121,122,126,127,128,129,130,131,
           132,136,137,138,139,140,141,142,143,144,149,150,151,152,153,154,155,156,159,164,165,166)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
    SET @DBName = @DBName + 1
END
IF @DBName IN (013,015,017,019,022,024,032,046,048,055,058,064,067,077,096,101,103,117,119,145,147,157,160,162)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
    SET @DBName = @DBName + 2
END
IF @DBName IN (029,070,080,113,123,133,167)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
    SET @DBName = @DBName + 3
END
IF @DBName IN (034,040)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
    SET @DBName = @DBName + 4
END
IF @DBName IN (006)
BEGIN
IF @DBName < 010 SET @statement = 'SELECT * FROM ' + 'a00' + @DBName + '0040.HTL00' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName BETWEEN 011 AND 099 SET @statement = 'SELECT * FROM ' + 'a0' + @DBName + '0040.HTL0' + @DBName + '.dbo.zz_tb_transactions_base'
IF @DBName > 099 SET @statement = 'SELECT * FROM ' + 'a' + @DBName + '0040.HTL' + @DBName + '.dbo.zz_tb_transactions_base'
    SET @DBName = @DBName + 5
END
SET @loopcounter = @loopcounter + 1

insert into #result values(@statement), ('union all')
END
SELECT * from #result

drop table #result
Comments