asked on

SQL - error in using a parameter in SQL Stored procedure

CREATE PROCEDURE FindDuplicateRecordIn
      -- Add the parameters for the stored procedure here
      @table nvarchar(256) = ' ',
      @column nvarchar(256) = ' '

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      SELECT * FROM @table GROUP BY @column
END

When I compile, I have the following error:

Msg 137, Level 15, State 2, Procedure FindDuplicateRecordIn, Line 16
Must declare the variable '@table'.
GO

I am not sure why, @table is pass down as an argument from the store procedure. I change the @table to @table1. Same error

Sharath S

I think you want to pass the table name and column name to the SP and get the result grouped on column name. try like this.

CREATE PROCEDURE FindDuplicateRecordIn
      -- Add the parameters for the stored procedure here
      @table nvarchar(256) = ' ', 
      @column nvarchar(256) = ' '

AS
BEGIN
     declare @sql nvarchar(500)
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      set @sql = 'SELECT * FROM ' + @table + 'GROUP BY ' + @column
      exec(@sql)
END

Open in new window

SOLUTION