Eugene Elutin (12/11/2012)
Sean Pearce (12/11/2012)
ALTER PROCEDURE [dbo].[usp_delete]@tablename sysname,
@pid int,
@pidname varchar(10)
AS
DECLARE @SQL NVARCHAR(MAX),
@params NVARCHAR(MAX);
SELECT@SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;',
@params = '@pid INT';
EXEC sp_executesql @SQL, @params, @pid = @pid;
Don't forget to test your sp by executing:
EXEC dbo].[usp_delete]
@tablename = 'sometable',
@pid int = 1,
@pidname = '1 = 1 OR 1 '
😎
It's a good idea to always protect your dynamic sql from injection
You can offer some protection by wrapping the execution statement.
ALTER PROCEDURE [dbo].[usp_delete]
@tablename sysname,
@pid int,
@pidname varchar(10)
AS
DECLARE @SQL NVARCHAR(MAX),
@params NVARCHAR(MAX);
SELECT@SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;',
@params = '@pid INT';
IF EXISTS(SELECT * FROM sys.columns WHERE name = @pidname AND OBJECT_NAME(object_id) = @tablename)
BEGIN
EXEC sp_executesql @SQL, @params, @pid = @pid;
END;
