Stored procedure gives an error

Question

I have an MSSQL stored procedure as shown 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

When executed, it gives the following error: Msg 156, level 15, state 1, line 1 Invalid syntax next to the keyword "BY". I also tried following, 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 the number of rows to be selected, @tablename represents the table name, @oby represents the column based on which ordering should be performed. Note. I am using ASP.Net with C # on the interface to run this procedure and use MSSQL 2008 R2 Express Edition on the backend

Answer 1

@tablename ORDER

Declare @sql varchar(max)=''

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

Print/Select dynamic sql

sql,

• @rowval INT
• QUOTENAME @tablename, sql
• SP_EXECUTESQL EXEC

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