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.