UDFs anf paramater variables

• Timothy MacKay

  SSC Enthusiast

  Points: 164

  More actions

  February 14, 2007 at 9:07 am

  #122981

  I am calling a scalar UDF from my select statement to fill a column:

  SELECT field1, field2, CASE WHEN SalesOrder.SOM_SalesOrderDate BETWEEN @StartDate AND dbo.fn_EQL_MakeDate(@EndDate, @InActPeriod) THEN SalesOrder.SOM_OrderValAmt ELSE 0 END AS ActiveVal

  When I try to call it like above with two @parameters I get "The Parameter is incorrect" message. If I switch out either parameter for a constant the function runs fine.

  I have GOOGLED this to death (from a myriad of angles) but not found an answer. I have no more hair to pull out, any help would be appreciated...

  Tim

  BTW - this all started when I had, what apparently was, the same issue with the DateAdd() function in the WHERE clause in passing two @parameters - any connection?

   

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  February 14, 2007 at 1:48 pm

  #689832

  What happens if you do it proper way?

  DECLARE @EndDay datetime

  SET @EndDay = dbo.fn_EQL_MakeDate(@EndDate, @InActPeriod)

  SELECT field1, field2, CASE WHEN SalesOrder.SOM_SalesOrderDate BETWEEN @StartDate AND @EndDay THEN SalesOrder.SOM_OrderValAmt ELSE 0 END AS ActiveVal

  _____________
  Code for TallyGenerator