Set table based on stored procedure parameter

Question

We have a process that updates certain tables based on a parameter passed in, specifically a certain state. I know organizationally this problem would be eliminated by using a single table for this data, but that is not an option -- this isn't my database.

To update these tables, we run a stored procedure. The only issue is that there was a stored procedure for each state, and this made code updates horrible. In order to minimize the amount of code needing to be maintained, we wanted to move towards a single stored procedure that takes in a state parameter, and updates the correct tables. We wanted this without 50 If statements, so the only way I could think to do this was to save the SQL code as text, and then execute the string. IE:

SET @SSQL = 'UPDATE TBL_' + @STATE +' SET BLAH = FOO' 
EXEC @SSQL;

I was wondering if there was a way to do this without using strings to update the correct tables based on that parameter. These stored procedures are thousands of lines long.

Thanks all!

score 0 · Answer 1 · answered Aug 26 '15 at 14:41

0

Instead save entire script as SQL text and execute it, just update the required table using like code below as where you need and rest continue as it is

EXEC('UPDATE TBL_' + @STATE +' SET BLAH = FOO')

answered Aug 26 '15 at 14:41

Raj Kamuni

370
1
12

score 0 · Answer 2 · answered Aug 26 '15 at 15:02

You could, indeed, use dynamic SQL (the exec function) - but with long, complex stored procedures, that can indeed be horrible.

When faced with a similar problem many years ago, we created the stored procedures by running a sort of "mail-merge". We'd write the procedure to work against a single table, then replace the table names with variables and used a PHP script to output a stored procedure for each table by storing the table names in a CSV file. You could replicate that in any scripting language of your choice - it took about a day to get this to work. It had the added benefit of allowing us to easily store the stored proc templates in source code control.

score 0 · Answer 3 · edited May 23 '17 at 11:51

You can safely use sp_executesql which is fairly more appropriate than a simple EXEC command. To do so, even with input and output parameters :

DECLARE @sql       nvarchar(4000),
        @tablename nvarchar(4000) = 'YOUR_TABLE_NAME',
        @params    nvarchar(4000),
        @count     int

SELECT @sql =
    N' UPDATE ' + @tablename +
    N' SET Bar = @Foo;' +
    N' SELECT @count = @@rowcount'

SELECT @params = 
    N'@Foo int, ' +
    N'@count int OUTPUT'

EXEC sp_executesql @sql, @params, 2, @count OUTPUT
SELECT @count [Row(s) updated]

I encourage you reading the related part of the article mentionned here.

Set table based on stored procedure parameter

3 Answers3