Member Avatar for spowel4

Is it possible to create a stored procedure to delete all records from a table (i.e. DELETE FROM <tablename>), but pass the table name to the procedure as a parameter? My desire is to have only one procedure for deleting multiple tables' records; I would simply pass in the table name as a parameter to the procedure. If this can be done, please provide examples, thanks!

  • 2 Contributors
  • 1 Reply
  • 187 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by adam_k
Member Avatar for adam_k

Yes it can be done by using dynamic SQL.

Here is a simple example:

declare @tablename varchar(20) 
declare @criteria varchar(500) 
set @tablename = 'table1' 
set @criteria = '' --you can set criteria if you wish, I'm leaving it blank, but using it in the command. 

declare @command varchar(2000) 
set @command = 'delete from ' + @tablename + ' ' + @criteria
exec(@command)

You can read more about execute here: http://msdn.microsoft.com/en-us/library/ms188332.aspx

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.