How to specify a table dynamically in a Stored Procedure

Question

Thanks for the feedback, but I was hoping for help with an UPDATE command, not SELECT. Can anyone help with the syntax for an UPDATE command?

I am passing a table name into a Stored Procedure but the SQL does not seem to recognize it.

DECLARE @userTable AS VARCHAR(200);
SET @userTable = @currTable


UPDATE @userTable
SET     [lang_String] = @lang_String, [date_Changed] = @submitDate1
WHERE   (ID = @ID)

@currTable is passed into the Stored Procedure. All tables names are built by design in code.

generally sql doesn't work that way and you'll need to use a dynamic sql function (almost all platforms have them). Without knowing your database though, it's hard to tell you what to do. — EBarr, Aug 01 '11 at 20:35
This is generally a SQL antipattern. If you don't know the tablename and columns before you write the code, you are doing somethign wrong. DO NOT try to write one update to handle all possible updates. There is no way I would allow code this bad to be put into any database I manage because it cannot be properly tested. Anytime you think about putting an action query (insert/update/Delete) in a dynamic query, you are putting your database at risk. — HLGEM, Aug 02 '11 at 18:35
Not when the variables that are passed in are static. they are locked into other tables set by an international standard. — htm11h, Aug 03 '11 at 20:13

score 5 · Accepted Answer · answered Aug 01 '11 at 20:35

5

You can't, you need to build the entire SQL string and then execute it, like this for example:

DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT col1, col2, col3 ' +
              ' FROM dbo.' + quotename(@tblname) +
              ' WHERE keycol = @key'

EXEC sp_executesql @sql, N'@key varchar(10)', @key

answered Aug 01 '11 at 20:35

E.J. Brennan

45,870
7
88
116

true, I made that assumption. – E.J. Brennan Aug 01 '11 at 20:37
Don't forget the `quotename()`! – Matt Brunell Aug 01 '11 at 20:46
can you provide the code, I cannot seem to get it to work. I ahve tried and get this error now on the date field. Conversion failed when converting date and/or time from character string. – htm11h Aug 01 '11 at 20:57
@user, you shouldn't edit other's answers to add to your question. You can edit your question instead. – svick Aug 01 '11 at 22:33
1

@user, that conversion error is due to your data and potential schema differences between the source and target columns in your update. Your question regarding how to dynamically specify a table name in an update has been answered by E.J. Brennan and I would recommend you create a separate question for the conversion error with your table schemas specified and sample data. – WT_W Aug 02 '11 at 03:14

score 0 · Answer 2 · answered Dec 04 '12 at 17:11

Got this to work quite easily....

  @myTable varchar(150)

/* Comments:
*/ 

AS  
SET NOCOUNT ON;   

DECLARE @sql varchar(max);   

SET @sql = 'SELECT [ID], [StringID], [GUID] FROM ' + @myTable + ' ORDER BY [GUID]';

print (@sql) 

EXECUTE(@sql);

SET @langTable = Null;

FYI, the values available for myTable are stored in another table and are not available to users for edit. Table names are built dynamically in code based on a unique combination of values.

How to specify a table dynamically in a Stored Procedure

2 Answers2