How to take table name as an input parameter to the stored procedure?

chandra sekhar picture chandra sekhar · Feb 28, 2014 · Viewed 60.4k times · Source

I have a small stored procedure below.

I am taking the table name as an input parameter to the stored procedure so that I'm planning to insert the data into the temp table and display the same. This is just a tiny code block of my project stored procedure.

When I am compiling the below, it is considering the parameter in the select statement as a table variable and throwing the error as:

Must declare the table variable "@TableName".

SQL:

CREATE PROCEDURE xyz @TableName Varchar(50) 
AS 
BEGIN 
SELECT TOP 10 * INTO #Temp_Table_One 
FROM @TableName 

SELECT * FROM #Temp_Table_One 
END

Answer

M.Ali picture M.Ali · Feb 28, 2014
CREATE PROCEDURE xyz 
@TableName NVARCHAR(128) 
AS 
BEGIN 
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'SELECT TOP 10 * INTO #Temp_Table_One 
              FROM ' + QUOTENAME(@TableName)
          + N' SELECT * FROM #Temp_Table_One '

 EXECUTE sp_executesql @Sql

END