Passing in a table name to stored procedure

• ctmfunk

  Grasshopper

  Points: 10

  More actions

  April 7, 2003 at 9:39 am

  #81305

  How do you pass in a table name to a stored procedure. I have a input parameter set up and I am trying to

  Select *

  From (input parameter, which is the tablename I want to slect from)

  When I attempt to execute, the error is the input parameter being used in the From statement must be declared... and it has been. Any suggestions? Thanks.

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  April 7, 2003 at 10:04 am

  #453360

  Sql*Server does not allow to reference variables in the from clause for table names.

  You need to use dynamic sql to execute the query. I have created an example for you.

  CREATE PROCEDURE DBO.db_SelectDynamic_rd

  (

  @p_tablename VARCHAR(32)

  )

  AS

  BEGIN

  DECLARE @mem_sql VARCHAR(255)

  SET @mem_sql = ""

  SET @mem_sql = @mem_sql + " SELECT * "

  SET @mem_sql = @mem_sql + " FROM " + @p_tablename

  EXEC (@mem_sql)

  END

  GO

  EXEC DBO.db_SelectDynamic_rd 'Employee'