CODEWITHSUNDEEP
sql-server
Nosheen Javed
Nosheen Javed

Reputation: 175

Using Table Name as a parameter in SQL Server?

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"

Upvotes: 0

Views: 121

Answers (3)

binarymnl
binarymnl

Reputation: 153

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.

Upvotes: 1

Dave C
Dave C

Reputation: 7392

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

Upvotes: 1

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

Try this

DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @table_name
EXEC sp_executesql @cmd

Upvotes: 2

Related Questions