Stored procedure gives an error

missing leading space before "order"

passing in tablename = fubar, the sql would become ...from fubarORDER BY...

using exec on SQL statements that are built from passed in parameters can be very dangerous. Please ensure that any data passed in is sanitized to prevent injection.

I personally find it simpler to debug cases where dynamic sql statements are built by setting them to a variable, that way the variable can be printed out.
Seeing the result printed often make the error more visible.

eg.

declare @s varchar(100)
set @s = 'Select top (' + @rowval + ') * from ' + @tablename + ' ORDER BY ' + @oby + ' DESC'

print @s
exec(@s)

-Richared

As already told, you missed a space before ORDER.

EXEC('Select top (' + @rowval + ') * from '+@tablename+ ' ORDER BY sno DESC')

The way you use parameters is opening the door to SQL injection.
https://en.wikipedia.org/wiki/SQL_injection[^]
http://www.w3schools.com/sql/sql_injection.asp[^]

[UpDate]

Quote:

Hi, I know what SQL Injection is and how it works.

We can't guess that you know what is this dangerous practice, and your usage if it don't make it less dangerous.

Quote:

What I need here is a solutions to this problem in the Stored Procedures that I have very limited knowledge about.

For your question, please read the first line of my answer, other solutions also tell you the same.

These general-purpose procedures are always more trouble than they're worth.

To prevent a SQL Injection[^] vulnerability, you need to validate both the table name and the "order by" column name. This is relatively easy when you have a single column name, with no "ASC" or "DESC" modifier. If you want to order by multiple columns, or control the sort sequence, this becomes much harder.

You'll also need to change the @rowval parameter to an integer, and use sp_executesql[^] to pass that to your dynamic query as a parameter. (The TOP operator can accept a variable, so you don't need dynamic SQL for that.)

Assuming @oby only contains a single column name, with no modifier, then something like this should work:

ALTER PROCEDURE [dbo].[GetDataFromTable]
(
    @rowval int,
    @tablename varchar(50),
    @oby varchar(50)
)
As
BEGIN
DECLARE @ObjectID int, @RealTableName sysname, @RealSchemaName sysname, @RealColumnName;
DECLARE @Query nvarchar(max), @Parameters nvarchar(max);
    
    SET NOCOUNT ON;
    SET @ObjectID = OBJECT_ID(@tablename);
    If @ObjectID Is Null RAISERROR('The table "%s" was not found.', 16, 1, @tablename);
    
    SELECT
        @RealTableName = QUOTENAME(T.name),
        @RealSchemaName = QUOTENAME(S.name)
    FROM
        sys.tables As T
        INNER JOIN sys.schemas As S
        ON S.schema_id = T.schema_id
    WHERE
        T.object_id = @ObjectID
    And
        T.type = 'U'
    ;
    
    If @@ROWCOUNT = 0 RAISERROR('The table "%s" was not found.', 16, 1, @tablename);
    
    SELECT
        @RealColumnName = QUOTENAME(name)
    FROM
        sys.columns
    WHERE
        object_id = @ObjectID
    And
        name = @oby
    ;
    
    If @@ROWCOUNT = 0 RAISERROR('The column "%s" was not found in the table "%s".', 16, 1, @oby, @tablename);
    
    SET @Query = N'SELECT TOP (@rowval) * FROM ' + @RealSchemaName + N'.' + @RealTableName + N' ORDER BY ' + @RealColumnName + N' DESC';
    SET @Parameters = N'@rowval int';
    
    EXEC sp_executesql @Query, @Parameters, @rowvalue = @rowval;
END

However, it would be a much better idea to create a separate procedure for each table you need to access.