Incorrect syntax error - TSql [Resolved]

Posted by Sharpcnet under Sql Server on 11/28/2013 | Points: 10 | Views : 5366 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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


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

0	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

0	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

0	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

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

0	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

Latest Posts