Stored procedure which has problem with the function return value

Question

Post reply

Stored procedure which has problem with the function return value

usha_infogenex

SSC Veteran

Points: 283
More actions
December 29, 2007 at 3:27 pm

#201966

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usha_infogenex SSC Veteran Points: 283 More actions · Answer 2

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'

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usha_infogenex SSC Veteran Points: 283 More actions · Answer 4

exec usp_rptsales '8/1/2007','policy'

This is how I am calling the stored procedure.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usha_infogenex SSC Veteran Points: 283 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usha_infogenex SSC Veteran Points: 283 More actions · Answer 8

Still I am not getting the result.

I tryed with Your piece code which you have send me.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)