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

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

3 Answers

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

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'

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