Stored procedure which has problem with the function return value

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Stored procedure which has problem with the...

Stored procedure which has problem with the function return value

Rate Topic

Display Mode

Topic Options

Author

Message

usha_infogenex

Posted Saturday, December 29, 2007 3:27 PM

Forum 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

Jeff Moden

Posted Saturday, December 29, 2007 6:13 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 33,108, Visits: 27,032

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #437363

usha_infogenex

Posted Saturday, December 29, 2007 7:00 PM

Forum 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

Jeff Moden

Posted Saturday, December 29, 2007 7:40 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 33,108, Visits: 27,032

Can you show the code that you're calling the sproc with, please?

--Jeff Moden

Post #437368

usha_infogenex

Posted Saturday, December 29, 2007 8:05 PM

Forum 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

Jeff Moden

Posted Saturday, December 29, 2007 8:09 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 33,108, Visits: 27,032

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

Post #437370

usha_infogenex

Posted Saturday, December 29, 2007 8:32 PM

Forum 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

Jeff Moden

Posted Sunday, December 30, 2007 10:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 33,108, Visits: 27,032

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

Post #437384

usha_infogenex

Posted Sunday, December 30, 2007 8:31 PM

Forum 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

Jeff Moden

Posted Sunday, December 30, 2007 10:02 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 33,108, Visits: 27,032

That's not a whole lot of information for me to go on...

--Jeff Moden

Post #437419

« Prev Topic | Next Topic »

Permissions