SQL Server 2008 R2: stored procedure with one or more table names a parameter

Question

I have this stored procedure to archive data which is older than a given number of days for a given table:

CREATE PROCEDURE [dbo].[sp_util_archive_test] 
    @days int, @table_name nvarchar(64)
AS
BEGIN TRY
BEGIN TRANSACTION
    DECLARE @archive_table varchar(128),
            @src_table varchar(128);
    SET @src_table = @table_name;
    SET @archive_table = @table_name + '_archive';

    DECLARE @dropSQL nvarchar(max) = 'DROP TABLE ' + @archive_table;

    IF OBJECT_ID(@archive_table, 'U') IS NOT NULL
      EXEC (@dropSQL);

    DECLARE @sqlCommand nvarchar(1000)
    DECLARE @date varchar(75)
    SET @sqlCommand = 'Select * into [' + @archive_table + ']  from  [' + @src_table + '] WHERE date <= dateadd(d, -' + CAST(@days AS varchar(16)) + ', getdate())'
    EXECUTE sp_executesql @sqlCommand

  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  DECLARE @Msg nvarchar(max)
  SELECT
    @Msg = ERROR_MESSAGE();
  RAISERROR ('Error Occured: %s', 20, 101, @Msg) WITH LOG;
END CATCH

Right now I have scheduled multiple SQL jobs to archive data for multiple tables, is there way to eliminate multiple SQL jobs by making the stored procedure to accept 1 or more table names and then to archive the data for all the table at once?

sounds like you want a table parameter which you'd then use in a cursor. But what if i were to pass in `talbeName]; drop table users;--` into your current procedure? — S3S, May 22 '17 at 20:15
the tables I am archiving are log tables, which are not worthy to maintain other than 7 days of data, so am not caring about the users in this context — OTUser, May 22 '17 at 20:17
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, May 22 '17 at 20:27
You are [missing what I was getting at](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — S3S, May 22 '17 at 20:27
To be more blatant, this is a risk of SQL injection. Having dynamic SQL is risky. — Kevin, May 22 '17 at 21:49

score 1 · Answer 1 · answered May 22 '17 at 22:11

Ignoring the risk of injection; You could pass through delimited strings into a parameter, and then loop through the results executing your existing stored procedure for each itteration.

e.g. @param = 'table1|Table2|Table3|Table4'

Split on "|" Loop through all results and execute your SP for each itteration.

Split example: How to split string and insert values into table in SQL Server

score 1 · Answer 2 · answered May 22 '17 at 23:16

Why complicate it? Either use multiple job steps or put multiple lines inside a single step:

EXEC [dbo].[sp_util_archive_test] 50, 'table1';
EXEC [dbo].[sp_util_archive_test] 70, 'table2';
EXEC [dbo].[sp_util_archive_test] 80, 'table3';
EXEC [dbo].[sp_util_archive_test] 10, 'table4';

SQL Server 2008 R2: stored procedure with one or more table names a parameter

2 Answers2