Use output of Stored Procedure as where condition

[BHTanna]

I have one stored procedure, which defines where condition based useing Case when...I m storing this Condition in one outpur variable of Store procedure.


I would like to now use this condition in view or Query...

Pls guide how to do this or is there any other way of doing the same?

Also note that, my case when defines entire condition .. It is not only giving criteria... like = or > part..

It is giving everthing after where...

Can anyone guide me...

Thanks

[debasisdas]

kindly post the code that you are working on for reference of our experts.

[BHTanna]

Pls find my process..

I m having one master table where i m having Customer id and its bill generation date (called BC). Another table is having customer id, payment amount and payment date(field name Pdate).

My requirement is to find, payment amount customer wise, but for closed BC. BC Closed exactly after one month. And that too, till date. I mean my till date is from BC to the day of today but that day will fall between BC and its closure..
That is called Last Month Same Date. (LMSD).

To do so, i m defining where condition which will be opt dynamically. for that i have written following process.


Create proc lmsd
@bc smalldatetime,
@con varchar(500) output
as

set @con = case when day(getdate()-1) > day(@bc) then
'day(pdate) between day(b.bc) and day(getdate()-1)'
else 'day(pate) between day(b.bc) and [dbo].[ufn_GetDaysInMonth](b.bc)
or day(pdate) between 1 and day(getdate()-1)' end


Now i want use this condition in where clause of my query / view.

Pls help.. Its bit URGENT.

[ck9663]

Convert it to function so that you can embed it on your query.

-- CK

[BHTanna]

Thanks...

Its working but now only problem i m facing in query and not able to resolve.

Pls help..

function

Create FUNCTION cont ( @bc DATETIME,@gt datetime )
RETURNS varchar(500)
AS
BEGIN

RETURN
case when day(@gt-1) > day(@bc) then
'day(coldate) between day(b.bc) and day(getdate()-1)'
else 'day(coldate) between day(b.bc) and [dbo].[ufn_GetDaysInMonth](b.bc) or day(coldate) between 1 and day(getdate()-1)'
end


END

Query


select bm.bc,max(coldate)
from gujsapcollection g inner join bcwise b on g.caf=b.caf
inner join bcmaster bm on bm.bc=b.bc
where coldate between b.bc and closedt
and [dbo].[cont](b.bc,getdate())

I m getting an error...

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.

[ck9663]

Your WHERE condition is incomplete:


Select bm.bc,max(coldate)
from gujsapcollection g inner join bcwise b on g.caf=b.caf
inner join bcmaster bm on bm.bc=b.bc
where coldate between b.bc and closedt
and [dbo].[cont](b.bc,getdate())


The highlighted part must be a boolean condition.

-- CK

[BHTanna]

But, this user defined function returns condition which will have boolean output...

This is the basic requirement to use dynamic where part...

[ck9663]

Nope. Your function is returning a string

"RETURNS varchar(500)"

-- CK

[BHTanna]

But that varchar string will be parse here in query no???

Wont the function will be replaced with its output string and entire query will be executed then after ???

Is there any way to check what exactly function in returning???

[ck9663]

Question:

your function basically says:

case 
   when day(@gt-1) > day(@bc) then
      'day(coldate) between day(b.bc) and day(getdate()-1)'
   else 
      'day(coldate) between day(b.bc) and [dbo].[ufn_GetDaysInMonth](b.bc) or day(coldate) between 1 and day(getdate()-1)'
end

if row 1 falls on WHEN part and record 2 falls on the ELSE part, will the two record be returned?

Is your condition one or the other? If it is, you might just use a UNION as a subquery.

-- CK

[BHTanna]

Let me tell you, what my function is doing exactly...

Here, getdate() is 13-Aug....

If BC date is 18-June i want payment made between 18-June and 12-July..
If BC date is 27-June i want payment made between 27-June and 12-July..
If BC date is 1-July i want payment made between 1-July and 12-July..
If BC date is 3-July i want payment made between 3-July and 12-July..
If BC date is 5-July i want payment made between 5-July and 12-July..
If BC date is 11-July i want payment made between 11-July and 12-July..

So, in first 2 cases, i need to take payment between 18-Jun and 30-Jun and 1-July to 12-July... That is my else conidtion

but for rest 4 cases, my first part of case when will work...

Pls guide me how should i adjust this condition in query...

Thanks 4 your kind support...