SP syntax

• smknox

  Hall of Fame

  Points: 3275

  More actions

  November 10, 2005 at 4:28 pm

  #169734

  Hi again.

  With the following syntax in a SP, I get an error that @TableName must be declared:

  =================================

  CREATE PROCEDURE usp_Table_Insert

   @SecondaryKeyValue int

   , @EmpName varchar(150)

   , @PrimaryKeyValue int OUTPUT

   , @ctlFKeyValue int

   , @TableName varchar(50)

   , @PrimaryKeyName varchar(100)

   , @SecondaryKeyName varchar(100)

   

  AS

  IF EXISTS

   ( SELECT

    @PrimaryKeyName  

   FROM @TableName

   WHERE  @SecondaryKeyName = @SecondaryKeyValue

  &nbsp

  RETURN

  =================================

  It looks to me like it's declared. What am I missing?

• [KH]

  SSCommitted

  Points: 1908

  More actions

  November 11, 2005 at 12:15 am

  #603368

  you can't use variable in the select from directly

  you have to do this

  declare

  @sql varchar(1000)

  select @sql = 'SELECT ' + @PrimaryKeyName + ' FROM ' + @TableName + ' WHERE ' + @SecondaryKeyName + ' = ' + @SecondaryKeyValue

  exec (@sql)

• smknox

  Hall of Fame

  Points: 3275

  More actions

  November 11, 2005 at 7:05 am

  #603407

  thanks again!!

  Sam