Incorrect syntax - T-Sql

Question

The idea is to have a generic stored procedure to get data from all tables. This query gives an error

Incorrect Syntax new @OrderByClause.

Where am I going wrong?

declare @TableName nvarchar(50), @ColName nvarchar(50), 
        @OrderByClause nvarchar(50), @Code nvarchar(max), 
        @StartIndex nvarchar(50), @EndIndex nvarchar(50)

set @TableName = 'tblCountry'
set @ColName = 'countryname'
set @OrderByClause = 'desc'
set @StartIndex = '2'
set @EndIndex = '10'

select @Code = 'With temp as (select row_number() 
               over (order by @ColName @OrderByClause) as row, * from @TableName)
               select * from temp where row between @StartIndex and @EndIndex'

set @param = '@TableName nvarchar(50), @ColName nvarchar(50), 
              @OrderByClause nvarchar(50), @StartIndex nvarchar(50), 
              @EndIndex nvarchar(50)'

execute sp_executesql @Code, @param @colname, @OrderByClause, @TableName, 
                      @StartIndex, @EndIndex

EDIT:

This is working though....

select @code = 'with temp as (select row_number() over (order by '+
                @colname+' '+@OrderByClause+') as row, * from '+@tablename+')
select * from temp where row between '+@startIndex+' and '+@EndIndex

execute sp_executesql @code

You can't parameterise arbitrary parts of the SQL as you are trying. The point of using dynamic SQL here is to create the desired string through concatenation first for the parts that can't be parameterised. Read up on SQL injection and the QUOTENAME function here — Martin Smith
– Martin Smith, Commented Nov 28, 2013 at 17:18

RBarryYoung · Accepted Answer · 2013-11-28 17:26:47Z

2

You missed a comma on the last statement:

execute sp_executesql @Code, @param @colname, @OrderByClause, @TableName,

should be

execute sp_executesql @Code, @param, @colname, @OrderByClause, @TableName,

The second thing is that @RahulTripathi was correct (but for a different reason), this is invalid:

select @Code = 'With temp as (select row_number() 
           over (order by @ColName @OrderByClause) as row, * from @TableName)
           select * from temp where row between @StartIndex and @EndIndex'

The @OrderByClause cannot be there because ASC and DESC are syntactical elements of the ORDER BY clause and cannot be variables.

Next, you have not defined @TableName correctly in the dynamic SQL. You are using it as a Table variable in the commands above, but you are passing (and defining) it as an NVarchar(50).

edited Nov 28, 2013 at 17:26

answered Nov 28, 2013 at 17:12

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martin Smith Over a year ago

That SQL string shown in the question ain't gonna work as desired either!

Ruby Over a year ago

oops my mistake, the comma is present in the query

RBarryYoung Over a year ago

@MartinSmith Oops. Yes, unlike T-SQL, I do short-circuit. :-)

ARA · Accepted Answer · 2013-11-28 19:47:03Z

I think your variables are not defined neither interpreted (in the case of desc) in the sp_executesql scope. Try to "expand" before calling sp_executesql with + and quotes when you assign @code:

select @Code = 'With temp as (select row_number() 
               over (order by '+ @ColName +' '+ @OrderByClause +' ) as row, * from '+ @TableName +')
               select * from temp where row between '+ @StartIndex + ' and '+@EndIndex

(did only one row)

or you could pass parameters to sp_executesql but you will have to choose different names and not for @ColName, @tableName and @OrderByClause I guess (you can't put variables instead of sql text anywhere in a query)

select @Code = 'With temp as (select row_number() 
               over (order by '+@ColName+' '+@OrderByClause+') as row, * from '+@TableName+')
               select * from temp where row between @pStartIndex and @pEndIndex'
execute sp_executesql @code, @pStartIndex=@StartIndex, @pEndIndex=@EndIndex

Collectives™ on Stack Overflow

Incorrect syntax - T-Sql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related