Using an Input Parameter as a Table name 1

[phbrady]

I am trying to use an input parameter as a table name in a stored proc. I am getting an error saying that the variable @dateid has not been declared.
Is there any way to use a table name that is dynamically created in the calling script and passed on to the sp as an input variable ?

CREATE PROCEDURE sp_ScoreCardsDrill1

@cocoid int,
@categoryid char (20),
@dateid char (20)

AS
SET NOCOUNT ON

IF @categoryid = 'NewClaims'

BEGIN
SELECT * FROM @dateid WHERE COCO_NO=@cocoid
END
GO

Thanks

 

[john76]

I don't believe that you can use a variable directly for a table name, but you might be able to construct the query dynamically as follows:

CREATE PROCEDURE sp_ScoreCardsDrill1

@cocoid int,
@categoryid char(20),
@dateid char(20)

AS
  SET NOCOUNT ON

  DECLARE @query varchar(250)

  IF @categoryid = 'NewClaims'
  BEGIN
    SET @query = 'SELECT * FROM '+@dateid+' WHERE COCO_NO = '+CONVERT(varchar, @cocoid)
    EXEC sp_executesql @query
  END
GO

Hope this helps,
John

 

[phbrady]

Thanks John, this is a great solution for my problem