December 29, 2007 at 3:27 pm
I have problem with the stored procedure.
stored procedure takes input parameters from a function return value.
Where the stored procedure is not taking the dynamic values from the function.
If I give the input parameters directly a static value then the stored procedure give the results.
Here is sample
December 29, 2007 at 6:13 pm
I believe you're missing the sample in your post...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2007 at 7:00 pm
Here the sample code.
Please help me out in this.
If I give the dates directly The stored procedure is giving the result is good.
If I give the dates with the variables ,I am not getting the result.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
--=============================================================================================
ALTER PROCEDURE dbo.usp_RptSales
@ParmAsOfDatedatetime,
@ParmVersionchar(6)
AS
SET NOCOUNT ON
DECLARE @AsOfBusinessDaysmallint
DECLARE @AsOfBeginDatedatetime
DECLARE @AsOfEndDatedatetime
DECLARE @AsOfEndDatePriordatetime
DECLARE @MonthBeginDatedatetime
DECLARE @MonthBeginDatePriordatetime
DECLARE @MonthEndDatedatetime
DECLARE @HealthProductCodevarchar(6)
SET @AsOfEndDate = dbo.uf_RptGetDayEndDate(@ParmAsOfDate)
SET @MonthBeginDate = dbo.uf_RptGetMonthBeginDate(@ParmAsOfDate)
SET @MonthEndDate = dbo.uf_RptGetMonthEndDate(@ParmAsOfDate)
SET @AsOfBusinessDay = dbo.uf_RptGetBusinessDay(@ParmAsOfDate, 'M')
SET @AsOfEndDatePrior = dbo.uf_RptGetDayEndDate(dbo.uf_RptGetBusinessDate(DATEADD(Month, -1, @MonthBeginDate) , @AsOfBusinessDay, 'M'))
IF @AsOfEndDatePrior IS NULL AND @AsOfBusinessDay > 0
BEGIN
SET @AsOfEndDatePrior = dbo.uf_RptGetMonthEndDate(DATEADD(Month, -1, @MonthBeginDate))
END
IF @AsOfEndDatePrior IS NULL AND @AsOfBusinessDay = 0
BEGIN
SET @AsOfEndDatePrior = dbo.uf_RptGetMonthBeginDate(DATEADD(Month, -1, @AsOfBeginDate))
END
SET @MonthBeginDatePrior = dbo.uf_RptGetMonthBeginDate(@AsOfEndDatePrior)
SELECTdistinct RptPolicy.ApplicationID,
xxxx.ApplicantState,
xxxx.dtTransactionCompletion,
xxxx.dtSubmitDate,
xxxx.PolicyStatus,
ProductGroupCode = convert(varchar(12), ProductGroupCode),
xxxx.DistributionChannelCode,
ISNULL(AORChannels.SubDCC,'A') as SubDCC,
--AORChannels.SubDccName,
SubmitDay= (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN @AsOfBeginDate AND @AsOfEndDate),
--SubmitDay= (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN 'Aug 17 2007 12:00AM' AND 'Aug 17 2007 11:59PM'),
IssueDay= (SELECT 1 WHERE xxxx.PolicyStatus = 'A' AND dtTransactionCompletion BETWEEN @AsOfBeginDate AND @AsOfEndDate),
SubmitMonth= (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN @MonthBeginDate AND @AsOfEndDate),
IssueMonth= (SELECT 1 WHERE xxxx.PolicyStatus = 'A' AND dtTransactionCompletion BETWEEN @MonthBeginDate AND @AsOfEndDate),
SubmitMonthPrior=
(SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN @MonthBeginDatePrior AND @AsOfEndDatePrior),
IssueMonthPrior=
(SELECT 1 WHERE xxxx.PolicyStatus = 'A' AND xxxx.dtTransactionCompletion BETWEEN @MonthBeginDatePrior AND @AsOfEndDatePrior),
ApplicantCount = dbo.uf_RptGetApplicantCount(RptPolicy.ApplicationID, 'Y'),
ParmAsOfDate = @ParmAsOfDate,
AsOfBeginDate = @AsOfBeginDate,
AsOfEndDate = @AsOfEndDate,
MonthBeginDate = @MonthBeginDate,
AsOfBusinessDay = @AsOfBusinessDay,
AsOfEndDatePrior = @AsOfEndDatePrior,
MonthBeginDatePrior = @MonthBeginDatePrior,
HealthProductCode,
IWPCode,
IWPApplicantCount = dbo.uf_RptGetIWPApplicantCount(RptPolicy.ApplicationID)
--------
INTOdbo.#RptTemp
FROMxxxx
left outer join dbo.xxxx on xxxx.AOR1=xxxx.AOR
left outer join xxxx prod on xxxx.applicationid=xxxx.applicationid
Where xxxx.dtSubmitDate BETWEEN 'Aug 17 2007 12:00AM'
AND 'Aug 17 2007 11:59AM' and PolicyStatus = 'A'
December 29, 2007 at 7:40 pm
Can you show the code that you're calling the sproc with, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2007 at 8:05 pm
exec usp_rptsales '8/1/2007','policy'
This is how I am calling the stored procedure.
December 29, 2007 at 8:09 pm
Ok... that's for the "If I give the dates directly The stored procedure is giving the result is good." part you were talking about... but you also said "If I give the dates with the variables ,I am not getting the result."... do you have an example of that call?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2007 at 8:32 pm
SubmitDay = (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN @AsOfBeginDate AND @AsOfEndDate),
--SubmitDay = (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN 'Aug 17 2007 12:00AM' AND 'Aug 17 2007 11:59PM'),
C these two lines.
1-with the variables.
2-with the direct dates.
so if I use line 1 I am not geting the correct result.
if I individually run the function where this variable is used.
It gets the date.
but in line 1 I am getting null value for submitday .
I have checked the function in this way.
DECLARE @AsOfBeginDatedatetime
DECLARE @ParmAsOfDatedatetime
set @ParmAsOfDate='09/01/2007'
SET @AsOfBeginDate = dbo.uf_RptGetDayBeginDate(@ParmAsOfDate)--@ParmAsOfDate)
Print @AsOfBeginDate.
December 30, 2007 at 10:08 am
DECLARE @AsOfBeginDate datetime
DECLARE @ParmAsOfDate datetime
set @ParmAsOfDate='09/01/2007'
SET @AsOfBeginDate = dbo.uf_RptGetDayBeginDate(@ParmAsOfDate)--@ParmAsOfDate)
Print @AsOfBeginDate
The item hilighted in red is incorrect. It must be a SELECT...
DECLARE @AsOfBeginDate datetime
DECLARE @ParmAsOfDate datetime
set @ParmAsOfDate='09/01/2007'
SET @AsOfBeginDate = (SELECT dbo.uf_RptGetDayBeginDate(@ParmAsOfDate))--@ParmAsOfDate)
Print @AsOfBeginDate
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2007 at 8:31 pm
Still I am not getting the result.
I tryed with Your piece code which you have send me.
December 30, 2007 at 10:02 pm
That's not a whole lot of information for me to go on...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply