This post shows you how to run a SELECT query in a stored procedure that takes as a parameter the name of the table. The code listing creates the stored procedure. After that you can execute the stored procedure and provide the name of a table in the current database.

There are those who warn against writing dynamic SQL. This is an example of dynamic SQL. The reason is you become vulnerable to attacks, particularly SQL injection. One safety precaution is to use QUOTENAME when you are building the query string.

When you provide the name of the table as a parameter, do not include the dbo. schema name in the parameter. So for example, if your table name is dbo.Bookstore, just type Bookstore for the parameter.

Also note that the following code line will not work, at least it didn’t for me. You need to use the sp_executesql stored procedure to run the sql string in the @query variable.

EXEC @query

There is a post at Stack Overflow discussing this.

Here is the code.

CREATE PROCEDURE SelectTableAsParam 
	@TableName as Varchar(100) 
AS 
BEGIN 
   DECLARE @query as nvarchar(200)  -- must be nvarchar not varchar
   SET @query = 'SELECT * FROM ' + QUOTENAME(@TableName)
   EXECUTE sp_executesql @query
END
GO