Question:
I would like to build a stored procedure that will receive a table name as a parameter. It then will select the data I want into whatever table name that I pass it.
How can I build a Select Into statement with a variable as a table name?
Answer:
You can create a stored procedure that uses the table name as a parameter. The key is to call the SQL EXEC (EXECUTE) statement. Below is the syntax to call the stored procedure (called DynamicTableName) and the script to create a bare-bones version of it that you can run in the Northwind database.
DynamicTableName 'Categories'CREATE PROCEDURE DynamicTableName @TableName varchar(255)ASSET NOCOUNT ONEXEC ('SELECT * FROM ' + @TableName)RETURN 0