September 20, 2006 at 8:36 am
Can you have a IF statement within a function.
Ex:
CREATE Function dbo.fnAFCompExitCTMgrAI(@YYear int , @MonthNum int, @userid varchar(10),@AorI varchar(1))
Returns Int
As
Begin
Declare @intAFCompExit Int
SELECT @intAFCompExit = Count(a.dteAFCompExit)
FROM dbo.table1 a, dbo.table2 b
WHERE DATENAME(year, a.dteAFCompExit) = @YYear And DATEPART(month, a.dteAFCompExit) = @MonthNum
and a.rNo = b.rNo
and CONVERT(VARCHAR(10), a.ProviderFYE, 110) = CONVERT(VARCHAR(10), b.dte, 110)
and b.Mgr = @userid
This next section is what I would like to add to the function.
and (if @AorI = 'A'
Begin
a.Ind = 'A' or a.Ind = 'F'
End
if @AorI = 'I'
Begin
a.Ind = 'I'
End
if @AorI = ''
Begin
a.Ind = 'A' or a.Ind = 'F'
End)
Return @intAFCompExit
End
September 20, 2006 at 10:41 am
use case function instead of If
September 20, 2006 at 11:01 am
can you give me an example of hoe this might be done?
September 20, 2006 at 11:33 am
try this for your problem
CREATE
Function dbo.fnAFCompExitCTMgrAI(@YYear int , @MonthNum int, @userid varchar(10),@AorI varchar(1))
Returns
Int
As
Begin
Declare
@intAFCompExit Int
SELECT
@intAFCompExit = Count(a.dteAFCompExit)
FROM dbo.table1 a, dbo.table2 b
WHERE DATENAME(year, a.dteAFCompExit) = @YYear And DATEPART(month, a.dteAFCompExit) = @MonthNum
and a.rNo = b.rNo
and CONVERT(VARCHAR(10), a.ProviderFYE, 110) = CONVERT(VARCHAR(10), b.dte, 110)
and b.Mgr = @userid
and (a.Ind= Case @AorI when 'A' then 'A'
when 'I' then 'I'
when '' then 'A'
End
or a.Ind=Case @AorI when 'A' then 'F'
when 'I' then 'I'
when '' then 'F'
End
September 21, 2006 at 5:39 am
Thanks, That worked great.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply