Stored procedure with table name as parameter

Question

I have a stored procedure, and I would like to assign the number of rows of that table to a variable and later use that variable.

I am calling the procedure like:

EXEC TEST.dbo.myProc nameOfTable

The procedure is something like:

CREATE PROCEDURE myProc @table_name varchar(1024) AS
BEGIN
    DECLARE  @Nval INT
    /*  SOME INSTRUCTIONS */

    SELECT   @Nval  = COUNT(*) FROM @table_name 
END

When executing I am getting an error:

Msg 156, Level 15, State 1, Procedure nLQ, Line 57
Incorrect syntax near the keyword 'FROM'.

How would I assign the variable @Nval?

score 15 · Answer 1 · answered May 04 '11 at 00:27

15

You can't parameterise a table name like that, FROM @table_name. Only way is to execute dynamic TSQL.

Before you do that, read: The Curse and Blessings of Dynamic SQL

answered May 04 '11 at 00:27

Mitch Wheat

295,962
43
465
541

And if I do it dinamically, How can I assign the count to a variable `Set @Statement = ' SELECT COUNT(*) FROM ' + @table_name; EXEC (@Statement);` where do I recover the value? – edgarmtze May 04 '11 at 01:27

score 10 · Accepted Answer · answered Jun 20 '13 at 08:09

try this

ALTER PROCEDURE [dbo].[sp_tablenametest]
@table_name varchar(50),
@PMId int,
@ValueEq int

AS
BEGIN
SET NOCOUNT ON;

DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @table_name + 
' WHERE Column1 = ''' + @PMId +  '''' +
' AND Column2= ''' + @ValueEq + ''''

EXEC sp_executesql @cmd 
END

Stored procedure with table name as parameter

2 Answers2

Linked