How can I use the table name as a parameter in a stored procedure?
I am doing it like this but I'm getting an error
ALTER PROCEDURE [dbo].[usp_TablesReporting_GetTableData]
@tableName as VARCHAR(100)
AS
BEGIN
SELECT * FROM @tablename
END
Error:
Must declare the table variable "@tableName"
Try this
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @table_name
EXEC sp_executesql @cmd
This is not possible in that format. You need to embed the variable into dynamic SQL and execute it.
DECLARE @TABLENAME NVARCHAR(100), @SQL NVARCHAR(4000)
SET @TABLENAME = 'myTable'
SET @SQL = 'SELECT * FROM ' + @TABLENAME
EXEC SP_EXECUTESQL @SQL
You can do something like below:
ALTER PROCEDURE [dbo].[usp_TablesReporting_GetTableData]
@tableName AS VARCHAR(100)= NULL -- NULL default value
AS
DECLARE @query AS NVARCHAR(max)= 'SELECT * FROM ' + @tablename
-- Validate the @tablename parameter.
IF @tablename IS NULL
BEGIN
PRINT 'ERROR: You must specify the table name.'
RETURN
END
exec sp_executesql @query
RETURN
GO
Here, i have just created and executed dynamic query.
To achieve in other manner using SqlCMD mode you can take reference from here.
