Functions and Dynamic SQL

• j saile

  Valued Member

  Points: 74

  More actions

  May 19, 2006 at 9:51 am

  #168945

  I'm just starting to play with User Defined Functions and Dynamic SQL.  I've created several functions and tested them just fine.  When I try to use them in dynamic sql as shown below, I get the error 'Must declare the variable '@elecTempID''. 

  -- DECLARATIONS

  declare @A7_Table varchar(25)

  declare @elecTempID int

  declare @gasTempID int

  declare @in_table varchar(50)

  declare @sql1 varchar(8000)

  -- ASSIGNMENTS

  set @a7_table = 'Planreport_06mar1'

  set @elecTempID = 124

  set @gasTempID = 125

  set @in_table = @A7_Table

  set @sql1 =  'select

    AISADM1.udf_templateid(@elecTempID, @gasTempID, product)

                  from AIS_Db.AISADM1.' + @in_table + ''

  exec(@sql1)

   

  It seems to me that I HAVE declared @elecTempID but obviously SQL Server thinks otherwise.    Can someone tell me what I'm missing?  To the extent that it matters, my intent is to ultimately roll this into a stored procedure.  Thanks.

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  May 19, 2006 at 10:21 am

  #639051

  Hmm... it ate my first response, let me try again (shorter version):

  EXEC() pulls out your dynamic SQL into a seperate context for execution. So things you declare in this context won't transfer over there.

  Your best bet is to use sp_executesql other than EXEC(). It allows you to pass in parameters to dynamic queries. The other option is to parse in your values like:

  set @sql1 = 'select

  AISADM1.udf_templateid(' + CONVERT(VARCHAR, @elecTempID) + ', ' + CONVERT(VARCHAR, @gasTempID) + ', product)

  from AIS_Db.AISADM1.' + @in_table + ''