Incorrect syntax error - TSql [Resolved]

Posted by Sharpcnet under Sql Server on 11/28/2013 | Points: 10 | Views : 4265 | Status : [Member] | Replies : 5
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






Responses

Posted by: Bandi on: 11/28/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

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

@StartIndex nvarchar(50), @EndIndex nvarchar(50) , @param NVARCHAR(4000)


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 +';'
-- PRINT(@code)
EXEC (@code)


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 11/28/2013 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Try giving space in OrderByClause value like this

set @OrderByClause = ' desc '


Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/28/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
go through the following link.. (When the query cannot be (fully) parameterized )
http://www.mssqltips.com/sqlservertip/2981/using-parameters-for-sql-server-queries-and-stored-procedures/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 11/29/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
There is some issue with you code:-

Just run the below code and you will get you required output.

DECLARE @TableName VARCHAR(50),
@ColName VARCHAR(50),
@OrderByClause VARCHAR(50),
@Code VARCHAR(MAX),
@StartIndex VARCHAR(50),
@EndIndex VARCHAR(50),
@QUERY VARCHAR(MAX)

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



SET @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 @QUERY = REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(@Code, '@TableName', @TableName)
, '@ColName', @ColName)
, '@OrderByClause', @OrderByClause)
, '@StartIndex', @StartIndex)
, '@EndIndex', @EndIndex)

EXECUTE (@QUERY)



Here if you want to select only required columns then see the below code:-

DECLARE @TableName VARCHAR(50),
@ColName VARCHAR(50),
@OrderByClause VARCHAR(50),
@Code VARCHAR(MAX),
@StartIndex VARCHAR(50),
@EndIndex VARCHAR(50),
@QUERY VARCHAR(MAX),
@ColumnName VARCHAR(500)

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


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

SET @QUERY = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Code, '@TableName', @TableName), '@ColName', @ColName), '@OrderByClause', @OrderByClause), '@StartIndex', @StartIndex), '@EndIndex', @EndIndex), '@ColumnName', @ColumnName)

EXECUTE (@QUERY)


If this helps you towards the solution, click on MARK IT AS ANSWER

Happy Coding.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/29/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
hi,
"Mark as answer "

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response