Incorrect syntax near the keyword 'set'

look at this:

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


set @SQL='SELECT * FROM     
  (SELECT row_number() OVER (ORDER BY order1 desc) AS num,* from     
  (
   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

Select allOpen in new window

It looks like Ritesh has taken care or your dynamic SQL syntax problems but I wanted to point something out and ask if you all know that your code:
where SUBJECT  like ''dest'' or SUBJECT  = ''dest'''
is really the same thing, if you don't have the % wild card in your like statement somewhere.  ie
where SUBJECT  like ''%dest%'' or SUBJECT  = ''dest'''

Hi riteshshah,
 
 I will check out this and post my result soon, I really want to know whether converting this to single whole lot of query in a variable,
 will it affect the performance during the result , will it get slow down . ?can u explain it please ....

 

well it depends, generally dynamic query will take bit more time than simple TSQL but in some case we have to go for dynamic SQL only. you can check execution plan of both query TSQL and dynamic TSQL and came to know the differences.

 I tried These three things but neither worked,
 
 I want to use varchar(max) only but i get this error,
 Try 1 : (As it is in the solution)
 
declare @SQL varchar(max)
 ........
 ...
exec sp_executesql @SQL  
 Error:
 Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
 
 Try 2 :
 if i used ntext, i get this error,
 declare @SQL ntext
  ........
  ...
 exec sp_executesql @SQL  
  Error:
  The text, ntext, and image data types are invalid for local variables.

 Try 3 :
 if i cast varchar to ntext
 
 declare @SQL varchar(max)
......
...
 exec sp_executesql cast(@SQL as ntext)
 
 Error:
 Incorrect syntax near '@SQL'.
 
 Nvarchar not to be used
i dont want to use nvarchar as its max is only 4000 , but my query will be generated more than 4000 char
 
 Please suggest the need
 
 Meeran03:)
 
 

if you are using only EXEC, varchar will work and if you are using sp_ExecuteSQL, there MUST nvarchar variable.

declare @str nvarchar(20)
declare @SQL  nvarchar(max)

set @str = 'test'
declare @start int
declare @end int

Set @start = 10
Set @end = 20

set @SQL=' 
SELECT * FROM     
  (SELECT row_number() OVER (ORDER BY order1 desc) AS num,* from     
  (

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   where num between ' + cast(@start as varchar) + ' and ' + 
cast(@end as varchar)
 

--print @sql
exec sp_executeSQL @SQL

Select allOpen in new window

hi riteshshah,

if i use nvarchar(max)my original sql get truncated to only 4000 character, but my query exceeds more than that , for that reason only am using varchar(max)here i can able to see overall query but am getting error  
 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. while executing
 
 is der any way to cast it ,,,??
because my query works when i take the copy paste that print statement but am not able to execute it ,,

Meeran03
 

Thanks Mate :)

""BtW, nvarchar(max) is not limited to 4000 character""

But when i used this , the print query was truncated one.