Creating a Stored Procedure

Is it possible to create a stored procedure that execute a delete command from a table whose name is specified as a parameter of the stored procedure?

Thank you

Yes. You would need to use dynamic SQL for that.

CREATE PROC....@.tblvarchar(25)AS...Declare @.sqlvarchar(100)--Build your SQLSET @.sql ='DELETE FROM ' + @.tbl +' WHERE <condition>'--Execute your SQLEXEC(@.sql)-- You can also use sp_ExecuteSQL. Check out BOL for more info.

|||

A problem with dynamic SQL though, is that by default the caller has to have delete permissions on the table. Will all callers have that permission? That could be a significant vulnerability in your application and database server.

What version of SQL Server are you using? If it's 2005, you could use the EXECUTE AS option in the stored procedure to run with another principal's permissions. That could go a long way towards making this more secure.

Don

|||

Thank you for the info.

A valuable one.