pass table variable to stored procedure

• Cleech

  Mr or Mrs. 500

  Points: 513

  More actions

  July 21, 2004 at 10:00 am

  #152234

  Hello All:

  I would like to pass a variable to a stored procedure,

  that would be a piece of a concatenated string to make up

  the table name.  The code I have so far is below.  The

  problem I'm having is in created the concatenated string

  (tbl??Product??final) for the table name and adding it to

  the SQL statement.  Can this be done? 

  CREATE PROCEDURE product

  (

  @product varchar(10),

  @TestPlan varchar(30)

  )

  AS

  SELECT * FROM  tbl??Product??final

  WHERE strTestPlan =  @TestPlan

  GO

  Any help is appreciated.

  Thanks,

  Joe

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 21, 2004 at 10:46 am

  #515499

  hey joe,

  you would have to resort to dynamic SQL to be able to do this.

  so your proc would look something like this:

  CREATE PROCEDURE dbo.usp_product

  (

    @product VARCHAR(10),

    @TestPlan VARCHAR(30)

  )

  AS

  BEGIN

    DECLARE @sqlStmt AS NVARCHAR(500)

    SET @sqlStmt = N'SELECT * FROM tbl' + CAST( @product AS NVARCHAR(10) ) + N'final WHERE strTestPlan = @TestPlan'

    EXEC sp_executesql @sqlStmt, N'@TestPlan VARCHAR(30)', @TestPlan

  END

  JP