Incorrect syntax near the keyword 'set'

Rajar Ahmed
Rajar Ahmed used Ask the Experts™
on
I would like to order my result set using the row_number function but am getting the error as like this,
how can i produce the result set with this set of query  .

Scenario :
I have to show the resultset 'between' query condition by descending of order1 column with ALL set of query
inside the row_number function..


am getting errors..
 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 ')'.


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

Set @start = 10
Set @end = 20

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   where num between @start and @end   

print @sql

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Open in new window

look at this one: sorry previous code belongs to your old question.

declare @str nvarchar(20)
declare @SQL  varchar(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

Open in new window

Chris LuttrellSenior Database Architect

Commented:
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'''
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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.

Author

Commented:

 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

Open in new window

Author

Commented:

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
 
if you are going to use varchar(max) than use EXEC (@SQL) rather than SP_ExecuteSQL.

BTW, nvarchar(max) is not limited to 4000 character

Author

Commented:
Thanks Mate :)

Author

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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial