CODEWITHSUNDEEP
c#asp.netsql-serversql-server-2008sql-server-2008-r2
ITSagar
ITSagar

Reputation: 703

Stored Procedure gives an error

I have a MSSQL stored procedure as below:

ALTER procedure [dbo].[GetDataFromTable]
(
@rowval varchar(50),
@tablename varchar(50),
@oby varchar(50)
)
as
begin
EXEC('Select top (' + @rowval + ') * from '+@tablename+ 'ORDER BY '+@oby+' DESC')
end

On execution, it gives following error: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'BY'. I tried following also, still the same error:

ALTER procedure [dbo].[GetDataFromTable]
(
@rowval varchar(50),
@tablename varchar(50),
@oby varchar(50)
)
as
begin
EXEC('Select top (' + @rowval + ') * from '+@tablename+ 'ORDER BY sno DESC')
end

Note: @rowval represents number of rows to be fetched, @tablename represents name of table, @oby represents column based on which ordering should be done. Note: I am using ASP.Net with C# at frontend to fire this procedure and using MSSQL 2008 R2 Express Edition at the backend

Upvotes: 4

Views: 84

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Give some space after @tablename and before ORDER

Declare @sql varchar(max)=''

SET @sql = 'Select top (' + @rowval + ') * from '+quotename(@tablename)+ ' ORDER BY sno DESC'
                                                                        --^here
EXEC (@sql)

Also start using Print/Select to debug the dynamic sql

To add some security to this dynamic sql I will make the following changes

  1. Make the @rowval as INT
  2. Use QUOTENAME function in @tablename parameter to avoid sql injection
  3. Will use SP_EXECUTESQL to execute the dynamic query instead of EXEC

Here is a correct way to do it

Declare @sql nvarchar(max)='',@tablename varchar(130), @rowval int 

select @sql  = 'Select top  (@rowval) * from '+quotename(@tablename)+ ' ORDER BY sno DESC'

exec sp_executesql @sql,N'@rowval int',@rowval = @rowval

Upvotes: 6

Related Questions