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

Resound

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 – Rutty

Possible duplicate of Passing table name in sql stored procedure – Betancourt

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

Answer 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

Mazel

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 . – Mazel

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

Is this any faster than sending a string as a SQL query? Put another way, what are the advantages of doing this rather than constructing a SQL statement string in your code & then running that query? – Wennerholn

Answer 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'

Whimper

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 :) – Resound

Answer 3

CREATE PROCEDURE sp_Get_Table
@Table_Name SYSNAME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DynamicSQL NVARCHAR(4000)
SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
EXECUTE sp_executesql @DynamicSQL
END
GO

EXEC sp_Get_Table 'tblUKCustomers'

This code will help you

Amberjack