Error-Must declare the table variable "@TableName"

Question

Hi

I am getting error - Must declare the table variable "@TableName"

CREATE PROCEDURE MaxDocEntry
	-- Add the parameters for the stored procedure here
      @TableName VARCHAR(50) = NULL
	  ,@DocEntry INT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT 
        @DocEntry = MAX(docentry) 
    FROM
        @TableName

END
GO

Prasad Raveendran · Answer

The error occurs because SQL Server does not support dynamic table names using variables directly in SQL queries. You cannot use @TableName as a table reference in a SELECT statement this way. To achieve this, you need to construct a dynamic SQL query using sp_executesql . Here's how you can modify your procedure: CREATE PROCEDURE MaxDocEntry @TableName VARCHAR(50) = NULL, @DocEntry INT OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @SQL NVARCHAR(MAX); -- Construct the dynamic SQL query SET @SQL = N'SELECT @DocEntryOutput = MAX(docentry) FROM ' + QUOTENAME(@TableName); -- Execute the dynamic SQL EXEC sp_executesql @SQL, N'@DocEntryOutput INT OUTPUT', @DocEntryOutput = @DocEntry OUTPUT; END GO Explanation: QUOTENAME(@TableName) ensures that the table name is properly escaped to avoid SQL injection issues. The sp_executesql procedure allows for the execution of dynamic SQL with parameterized queries. We pass @DocEntry as an output parameter to capture the result of the MAX(docentry) query. This should resolve the error and allow you to dynamically query the table specified by @TableName .