asked on

oopss.Incorrect syntax near the keyword 'union',Incorrect syntax near the keyword 'if'. HAving NO ClUE

Hi ,

i want to get worked this union condition with the if else condition , but am stuck with these error . Please gimme a solution which involves logic wit union
condition as it is mentioned below .

Scenario,
I need to generate a seperate select statement with
where condition SUBJECT query and make union of the other queries . i want to have all queries which i mentioned below .
MY only problem , i dunno how to do union with the one will be generated in if else loop and am getting all these errorss.....
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'if'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'union'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'union'.

declare @str nvarchar(20)
set @str = 'test'
select * from tbl1 where cname='jame'        
Union       
if (@str = 'test')
begin
select * from tbl1 where SUBJECT  like 'dest' or SUBJECT  = 'dest'       
end
else
begin
select * from tbl1 where SUBJECT like 'depa' or SUBJECT = 'depa'
end  
union
select * from tbl1 where cname like 'nape'
union
select * from tbl1 where cname like 'uesed'

Open in new window

RiteshShah

you should do something like this:

declare @str nvarchar(20)
declare @SQL nvarchar(max)
set @str = 'test'

set @SQL=' select * from tbl1 where cname=''jame''        
Union '      
if (@str = 'test')
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''       
end
else
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end  
print @sql
exec (@sql)

Open in new window

Seeker1983

Hi,

Instead of trying to do a union with an if (which I don't think will work) add a where clause to each of the union statements with @str = 'test'.
This will only return the unioned data if the @Str matches your test.

RiteshShah

see updated version.

declare @str nvarchar(20)
declare @SQL nvarchar(max)
set @str = 'test'

set @SQL=' select * from tbl1 where cname=''jame''        
Union '      
if (@str = 'test')
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''       
end
else
begin
	set @SQL= @SQL + ' select * from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end  

set @SQL= @SQL + ' union
select * from tbl1 where cname like ''nape''
union
select * from tbl1 where cname like ''uesed'''

print @sql
--exec (@sql)
--exec sp_executeSQL @sql

Open in new window

Rajar Ahmed

ASKER

hi Riteshshah,

It was good to see that print statement
but am not gud in sql can u plz explain ,
what u did each step , Because i see so many red color in that query ??

Am unable to understand..?

Thanks For ur timely help

I will test ur query now , and post my result soon..!!

Meeran03:)

RiteshShah

comment print statement and execute query like this.

declare @str nvarchar(20)
declare @SQL nvarchar(max)
set @str = 'test'

set @SQL=' select * from tbl1 where cname=''jame''        
Union '      
if (@str = 'test')
begin
        set @SQL= @SQL + ' select * from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''       
end
else
begin
        set @SQL= @SQL + ' select * from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end  

set @SQL= @SQL + ' union
select * from tbl1 where cname like ''nape''
union
select * from tbl1 where cname like ''uesed'''

--print @sql
--exec (@sql)
exec sp_executeSQL @sql

Open in new window

RiteshShah

I did nothing but generate dynamic SQL in one variable named @SQL, which can be executed either by sp_executeSQL or by EXEC. nothing much. try to run the query and let me know.

Éric Moreau

can you try this one which does not involve dynamic SQL:

declare @str nvarchar(20) 
set @str = 'test' 
select * from tbl1 where cname='jame'         
Union        
select * from tbl1 where SUBJECT  like case @str when 'test' then 'dest' else 'depa' end 
union 
select * from tbl1 where cname like 'nape' 
union 
select * from tbl1 where cname like 'uesed'

Open in new window

Rajar Ahmed

ASKER

Hi ritestshah,

I wud like to order my result , is it possible to do this way ...
I get this error , when i try this way ....

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'set'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ')'.

Meeran03

declare @str nvarchar(20)
declare @SQL  varchar(max)
set @str = 'test'

SELECT * FROM     
  (SELECT row_number() OVER (ORDER BY order1 desc) AS num,* from     
  (
set @SQL=' select ''1'' as order1,* from tbl1 where cname=''jame''       
Union '     
if (@str = 'test')
begin
    set @SQL= @SQL + ' select ''2'' as order1,* from tbl1 where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''      
end
else
begin
    set @SQL= @SQL + ' select ''2'' as order1,* from tbl1 where SUBJECT like ''depa'' or SUBJECT = ''depa'''
end 

set @SQL= @SQL + ' union
select ''3'' as order1,* from tbl1 where cname like ''nape''
union
select ''4'' as order1,* from tbl1 where cname like ''uesed'''
 ) test1) test2     

print @sql

Open in new window

ASKER CERTIFIED SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajar Ahmed

ASKER

hi riteshshah,

It seems like i had added more issues from which what i had asked ...I had posted the same in the another question .

This is my question :
https://www.experts-exchange.com/questions/25096603/Incorrect-syntax-near-the-keyword-'set'.html

Meeran 03

RiteshShah

ok than please check my previous answer and close this question as it had resolved the issue (errors) you mentioned above.