-1

I am using SQL Server 2017 and I would like to create a stored procedure with a single table name as an input variable. All I want the procedure to do is update that table in a variety of ways. This project will be done twice a year, and the columns will always be the same, so I would like to try this as a stored procedure, so I do not have to highlight several lines of code and executing each time.

Is there a simple way to pass a table name through a stored procedure which updates the table (adding columns, calculating columns, replacing nulls in columns etc). In a basic example, one task would be just replaces nulls with 0s in a column. I am not sure how to set this up though. DO I have to declare every column in the table too? 

CREATE PROCEDURE updteleform  
    @tablename TABLE
AS
BEGIN
    UPDATE @tablename
    SET Recog = 0
    WHERE Recog IS NULL
END
GO
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
user9084595
  • 85
  • 1
  • 1
  • 5
  • 2
    Sounds like you are dealing with a single table, so you haven't really parameterized the right thing. I think you should have a stored procedure for each action, and each stored procedure references the table directly. If you perform the same set of actions against different tables, create the same set of stored procedures for that table. Don't take "don't repeat yourself" too seriously. – Aaron Bertrand May 02 '18 at 16:08
  • Possible duplicate of [How to take table name as an input parameter to the stored procedure?](https://stackoverflow.com/questions/22105121/how-to-take-table-name-as-an-input-parameter-to-the-stored-procedure) – Tab Alleman May 02 '18 at 17:21

1 Answers1

1

I'm assuming you want to update a physical table. SQL Server table variables don't work that way, rather they are a way to pass a transient result set to a stored procedure. There is no persistence if your stored procedure does not do so.

If you are looking to update the same table, then just write the procedure to work on that table.

If you are looking to update multiple tables using the same script then you should change your procedure to accept a string parameter that would be the name of the table you want it to work on and then use dynamic SQL in your stored procedure.

Something like

CREATE PROCEDURE updteleform  @tablename sysname
AS
BEGIN

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'
update ' + QUOTENAME(@tablename) + '
set Recog= 0
where Recog is null;';

EXEC sp_executesql @sql;

END
GO

And then call it with something like:

EXEC updteleform @tablename = 'table1';
EXEC updteleform @tablename = 'table2';
EXEC updteleform @tablename = 'table3';
...
squillman
  • 13,363
  • 3
  • 41
  • 60
  • thank you- this helps clear up some confusion. Yes I would like to update a physical table. Do you have any pointers how I would set up a stored procedure to use dynamic sql to update different tables (over time) with the same script? – user9084595 May 02 '18 at 16:11
  • The example in my answer is how to do it with dynamic SQL. – squillman May 02 '18 at 16:13
  • 1
    You need to wrap @tablename with quotename at the very least to help prevent sql injection. – Sean Lange May 02 '18 at 16:13
  • 2
    @user9084595 Why do you need a single stored procedure to handle all these different against against a different number of tables? Do all your tables have the exact same structure? Will actions never be different depending on the table, or valid for one table but not another? What is the advantage of having a single stored procedure that will perform any action against any table? – Aaron Bertrand May 02 '18 at 16:15
  • 4
    @AaronBertrand I suspect they have tables broken apart by customer or something along those lines and they are wanting to update all of those tables. The need for this type of thing screams of normalization issues to me. Seems like a column in the table instead of dozens of identical tables would have prevented this situation in the first place. – Sean Lange May 02 '18 at 16:17
  • thank you for your help. yes this will work. if it is test scores and the tests are taken twice a year, then basically, I am performing the same set of procedures for different groups of students. – user9084595 May 04 '18 at 14:28