using dynamic columns

• Donald Leaderach

  SSC-Addicted

  Points: 478

  More actions

  September 22, 2006 at 1:12 pm

  #172769

  I have a stored procedure that uses a function to populate a dynamic field.

  I would like to use the new filed in the WHERE clause but it is not recognized.

  I ended up using the function again.

  Is there a way to use the new field in the WHERE clause.

  Below is a portion of the proc.

  SELECT ISNULL(a.YYear, a.YYear) as YYear,

     ISNULL(a.MonthNum, a.MonthNum) as MonthNum,

     ISNULL(a.MonthName, a.MonthName) as MonthName, 

     dbo.fnAFCompExitCTAI(a.YYear, a.MonthNum, ''' + @varAorI + ''')As AuditCount

  From AuditDates a

     WHERE  1 = 1

     and (YYear + str(MonthNum,2,0)) >= ('''+ @varYearStart + ''' + str(''' + @varMonthStart + ''',2,0)) 

   and (YYear + str(MonthNum,2,0)) <= (''' + @varYearEnd + ''' + str(''' + @varMonthEnd + ''',2,0))'

  and dbo.fnAFCompExitCTAI(a.YYear, a.MonthNum, ''' + @varAorI + ''') <> 0

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 22, 2006 at 2:23 pm

  #662036

  if you want to use where AuditCount = ....

   

  No you can't do that.  You have to use a derived table, then you can use the new colum alias.

  However if the function is deterministic I wouldn't expect any performance hit with the query you currently have.