Problem while using union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem while using union

Author

Topic

ibin
Starting Member

26 Posts

Posted - 2009-10-14 : 01:11:43

Hi..

I am trying to do a union as below but i get an error stating incorrect syntax near IF.Is there any restriction of not using the if statement with Union.

CODE:
if Exists(select * from information_schema.columns where table_Name = N'Temptable' and column_name = N'test col')
begin
exec sp_executesql N'Select [linkid] as IDLinkItem, [test col] as col1.'' as col3 from Temptable'
union
end
if Exists(select * from information_schema.columns where table_Name = N'Temptable' and column_name = N'test col1')
begin
exec sp_executesql N'Select [linkid] as IDLinkItem, [test col1] as col1.'' as col3 from Temptable'
union
end
---goes on...

Or any optimal solutions sample code plz...

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-14 : 01:21:32

You can use UNION only for a Select Statement and not for executing a stored procudure. Here you can achieve the functionality by using a temp table.

if Exists(select * from information_schema.columns where table_Name = N'Temptable' and column_name = N'test col')
begin
insert into #tmp exec sp_executesql N'Select [linkid] as IDLinkItem, [test col] as col1.'' as col3 from Temptable'
end
if Exists(select * from information_schema.columns where table_Name = N'Temptable' and column_name = N'test col1')
begin
insert into #tmp exec sp_executesql N'Select [linkid] as IDLinkItem, [test col1] as col1.'' as col3 from Temptable'
end
---goes on...

and lastly,

Select * from #tmp

ibin
Starting Member

26 Posts

Posted - 2009-10-14 : 04:29:50

Thanks..!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources