This is the script I am using to run the function:
-- This just runs forever
DECLARE @dtDateEnd datetime
SELECT @dtDateEnd = '03/05/04'
SELECT * FROM [dbo].[fnCMStatsMTD_Summary](@dtDateEnd)
--- This work fine
SELECT * FROM [dbo].[fnCMStatsMTD_Summary]('03/05/04')
--If I hard code the date in the procedure it runs fine (50sec)
ALTER PROCEDURE usp_CMStats_MTDTest @dtDateEnd datetime
AS
SELECT fnAdmissionsMTD_CR_CM.Agency,
fnAdmissionsMTD_CR_CM.Team_Number, fnAdmissionsMTD_CR_CM.Case_Number,
fnAdmissionsMTD_CR_CM.Last_Name, fnAdmissionsMTD_CR_CM.First_Name,
fnCMStatsMTD_Summary.F2F, fnCMStatsMTD_Summary.Collateral
FROM dbo.fnAdmissionsMTD_CR_CM('3/5/04') fnAdmissionsMTD_CR_CM
LEFT OUTER JOIN dbo.fnCMStatsMTD_Summary('3/5/04') fnCMStatsMTD_Summary
ON fnAdmissionsMTD_CR_CM.Consumer_ID = fnCMStatsMTD_Summary.Consumer_ID
--If I try to pass the date time variable to the functions in the procedure it runs forever.
ALTER PROCEDURE usp_CMStats_MTDTest @dtDateEnd datetime
AS
SELECT fnAdmissionsMTD_CR_CM.Agency,
fnAdmissionsMTD_CR_CM.Team_Number, fnAdmissionsMTD_CR_CM.Case_Number,
fnAdmissionsMTD_CR_CM.Last_Name, fnAdmissionsMTD_CR_CM.First_Name,
fnCMStatsMTD_Summary.F2F, fnCMStatsMTD_Summary.Collateral
FROM dbo.fnAdmissionsMTD_CR_CM(@dtDateEnd) fnAdmissionsMTD_CR_CM
LEFT OUTER JOIN dbo.fnCMStatsMTD_Summary(@dtDateEnd) fnCMStatsMTD_Summary
ON fnAdmissionsMTD_CR_CM.Consumer_ID = fnCMStatsMTD_Summary.Consumer_ID
The UDF is a parameterized view that uses the date passed to select records by a date field.
Is there some coversion or something that needs to be done to the variable is the procedure to get it to pass to the function?