CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Ruby
Ruby

Reputation: 969

Incorrect syntax - T-Sql

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

Upvotes: 0

Views: 140

Answers (2)

ARA
ARA

Reputation: 1316

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

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56725

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).

Upvotes: 2

Related Questions