Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure which has problem with the function return value Expand / Collapse
Author
Message
Posted Saturday, December 29, 2007 3:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2007 4:56 AM
Points: 5, Visits: 8
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
Post #437357
Posted Saturday, December 29, 2007 6:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
I believe you're missing the sample in your post...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437363
Posted Saturday, December 29, 2007 7:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2007 4:56 AM
Points: 5, Visits: 8
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
@ParmAsOfDate datetime,
@ParmVersion char(6)
AS
SET NOCOUNT ON
DECLARE @AsOfBusinessDay smallint
DECLARE @AsOfBeginDate datetime
DECLARE @AsOfEndDate datetime
DECLARE @AsOfEndDatePrior datetime
DECLARE @MonthBeginDate datetime
DECLARE @MonthBeginDatePrior datetime
DECLARE @MonthEndDate datetime
DECLARE @HealthProductCode varchar(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)
SELECT distinct 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)
--------
INTO dbo.#RptTemp
FROM xxxx
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'
Post #437364
Posted Saturday, December 29, 2007 7:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Can you show the code that you're calling the sproc with, please?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437368
Posted Saturday, December 29, 2007 8:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2007 4:56 AM
Points: 5, Visits: 8
exec usp_rptsales '8/1/2007','policy'
This is how I am calling the stored procedure.
Post #437369
Posted Saturday, December 29, 2007 8:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437370
Posted Saturday, December 29, 2007 8:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2007 4:56 AM
Points: 5, Visits: 8
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 @AsOfBeginDate datetime
DECLARE @ParmAsOfDate datetime
set @ParmAsOfDate='09/01/2007'
SET @AsOfBeginDate = dbo.uf_RptGetDayBeginDate(@ParmAsOfDate)--@ParmAsOfDate)
Print @AsOfBeginDate.
Post #437371
Posted Sunday, December 30, 2007 10:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437384
Posted Sunday, December 30, 2007 8:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 31, 2007 4:56 AM
Points: 5, Visits: 8
Still I am not getting the result.
I tryed with Your piece code which you have send me.
Post #437415
Posted Sunday, December 30, 2007 10:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
That's not a whole lot of information for me to go on...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse