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

Question

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

Solution 1

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

Solution 2

use dynamic sql

try

CREATE PROCEDURE xyz @TableName VARCHAR(50) 
AS 
BEGIN 
 DECLARE @query VARCHAR(1000)
 set @query = 'SELECT TOP 10 * FROM '+ @TableName 
 EXEC (@query)
END

add schema name.

eg:

exec xyz @TableName = 'dbo.mytable'

exec xyz @TableName = 'myschema.mytable'

Comments

[dburges]

they have given you the solution but note that this is generally a bad idea and you should not write code this way just to avoid writingm ore than one proc. Necver write dynamic sql until you read and understand thoroughly this article: sommarskog.se/dynamic_sql.html

[Ian Ringrose]

Possible duplicate of Passing table name in sql stored procedure

[Michael Freidgeim]

Does this answer your question? How should I pass a table name into a stored proc?

[chandra sekhar]

Thanks Luis. Thanks for the quick response. When i am compiling this it is fine. But when i am executing the SP by supplying table name as parameter value, it is throwing one more error called 'Could not find stored procedure 'SELECT TOP 10 * FROM dbo.Employee_One'.' One minor change in your procedure. You forgot to provide the datatype while declaring @query. No problem i tried by providing varchar(100). The below reply from ALi is working fine for my requirement. Anyways thanks for spending your valuable time for me. Thanks a lot. Cheers :)

[M.Ali]

I would disagree with you, @TCS this is 100% safe code. I have used QUOTENAME function which puts square brackets [] around the passed parameter and forces sql server to treat it as an object name. If you try to pass some sort of sql command in the parameter it will be treated as an object name and error out. but you will never be a victim of Sql-injection .

[Rodrigo Recio]

When I do this I get an error saying that QUOTENAME is unresolved. Can anyone point me in a direction?