Calling a function within a sproc yields diff result than calling the function

• Mark Eckeard

  Hall of Fame

  Points: 3462

  More actions

  November 3, 2016 at 8:03 am

  #315207

  Hi all,

  I have a scalar-valued function that does a calculation and returns a float value. Works perfectly when I call it like this in a query window:

  declare @intNumber float

  set @intNumber = dbo.f_GetTodaysTotalCrossSellByNetworkID2('patt3102', '2016-09-22');

  select @intNumber

  The current result is 12 and is correct. Now, when I call it within a sproc, it returns 18 for some reason. Here's the call in the sproc:

  declare @Test_Value float

  set @Test_Value = dbo.f_GetTodaysTotalCrossSellByNetworkID2(@Network_ID, @Target_Date);

  @Test_Value is an output param within the sproc and a float, so I don't think that's the problem. Also, the 2 params being passed in to the function within the sproc are the same as the hard-coded ones in the first example. I double checked that.

  Does anyone know why I would get a different value when calling my function within the sproc?

  Thanks,

  Mark

• RTaylor2208

  SSChampion

  Points: 13298

  More actions

  November 3, 2016 at 8:15 am

  #1910363

  In the function what data types are used for the two parameters passed?

  In the procedure are the datatypes used for @Network_ID, @Target_Date the same types as used in the function?

  Can you post up your function and stored procedure definitions?

  Lastly if you create a copy of the procedure and hard core the values from this:

  declare @Test_Value float

  set @Test_Value = dbo.f_GetTodaysTotalCrossSellByNetworkID2(@Network_ID, @Target_Date);

  to this:

  declare @Test_Value float

  set @Test_Value = dbo.f_GetTodaysTotalCrossSellByNetworkID2('patt3102', '2016-09-22');

  Does the result return the same as calling only the function as a query?

  MCITP SQL 2005, MCSA SQL 2012