Search code examples
sqlstored-proceduressql-server-2008-express

How to specify a table dynamically in a Stored Procedure

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.

Solution

  • 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