Create a user defined function?

Question

I'm trying to create an user defined function with take 2 parameters the federal filing status char 1 where have data like S and M which are single and married and the second parameter is state char 2 where have data created already in a table EmployeeTransfers have MS,FL,NY etc..

I have create a functoin that returns one of the code which is of data type char 1 with this logic example: IF State = NY and Federal Filing Single(S) then Code is A or Federal Filing Married(M) then Code is B

etc...

so there my code my returns are NULL i dnt knw how to set the return more specific

create function dbo.GetStateFilingStatus
(
    @federalfilingstatus char(1),
    @state char(2)
) returns char(1)
as
begin
    declare @code char(1) = null

    select @federalfilingstatus = EmpFederalFilingStatus from EmployeeTransfers
    where @state  = EmpTransferState

    if (@state = 'MS' AND @federalfilingstatus = 'S')
    set @code = 'A'
    return @code 

    if (@state = 'MS' AND @federalfilingstatus = 'M')
    set @code = 'M'
    return @code 

    if (@state = 'NJ' AND @federalfilingstatus = 'S')
    set @code = 'B'
    return @code 

    if (@state = 'NJ' AND @federalfilingstatus = 'M')
    set @code = 'A'
    return @code 

    if (@state = 'AZ' AND @federalfilingstatus = 'S')
    set @code = 'A'
    return @code 

    if (@state = 'AZ' AND @federalfilingstatus = 'M')
    set @code = 'B'
    return @code 

    if (@state = 'CT' AND @federalfilingstatus = 'S')
    set @code = 'F'
    return @code 

    if (@state = 'CT' AND @federalfilingstatus = 'M')
    set @code = 'M'
    return @code 

    if (@state = 'DC' AND @federalfilingstatus = 'S')
    set @code = 'S'
    return @code 

    if (@state = 'DC' AND @federalfilingstatus = 'M')
    set @code = 'Y'
    return @code 

end
Go

I tried to execute it with

select dbo.GetStateFilingStatus(EmpTransferState ,EmpFederalFilingStatus) as StateFilingStatus from EmployeeTransfers

my returns are nulls.

Any reason you have tagged MySQLand SQL Server?

DavidG
– DavidG

2015-11-17 00:13:16 +00:00
Commented Nov 17, 2015 at 0:13 — DavidG
– DavidG, Commented Nov 17, 2015 at 0:13

Rabbit · Accepted Answer · 2015-11-17 00:17:08Z

3

Your code is returning at the very first return because it doesn't know it's part of the if block. Just remove all the returns and have one return at the very end.

A better way of doing this is to create a lookup table that you can join to. That way, if you ever need to add a filing status or state, you can just add a row. And if you need to modify a code, you can just update the table.

answered Nov 17, 2015 at 0:17

Rabbit

5073 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick.Mc Over a year ago

I agree. Don't use a UDF (unless this is another dopey homework question). Use a table

The 21 Over a year ago

Thank for the lesson ,also i noticed other problem that was the order of parameter so i fixed write state first and then federalfilingstatus because the if was reverse order i wrote first state and federalfilistatus which the parameter were reverse

DavidG · Accepted Answer · 2021-11-20 09:11:17Z

2

You are missing BEGIN and END statements after the IF, otherwise SQL will execute treat the first statement as being inside the IF block and the rest after. Anyway, you don't need to use the @code variable here at all, just return the value directly:

if (@state = 'MS' AND @federalfilingstatus = 'S')
    return 'A' 

if (@state = 'MS' AND @federalfilingstatus = 'M')
    return 'M' 

if (@state = 'NJ' AND @federalfilingstatus = 'S')
    return 'B' 

--etc...

A better solution however would be to use a lookup table instead of this complicated list of conditionals. That way you can add to it and not have to worry about maintaining a complex stored procedure.

edited Nov 20, 2021 at 9:11

user10563627

answered Nov 17, 2015 at 0:29

DavidG

120k13 gold badges232 silver badges239 bronze badges

1 Comment

The 21 Over a year ago

Thank for the lesson ,also i noticed other problem that was the order of parameter so i fixed write state first and then federalfilingstatus because the if was reverse order i wrote first state and federalfilistatus which the parameter were reverse

Felix Pamittan · Accepted Answer · 2015-11-17 00:15:21Z

1

You need to add BEGIN and END in your IF statements:

if (@state = 'MS' AND @federalfilingstatus = 'S') begin
    set @code = 'A'
    return @code 
end

What happened before is that, without the BEGIN - END statements the RETURN statement is executed after evaluating the first IF statement.

Alternatively, you can use IF - ELSE IF combination to set the value of @code and put the RETURN statement at the end.

IF <condition> BEGIN
    SET @code = 'A'
ELSE IF <condition> BEGIN
    SET @code = 'B'    
END

RETURN @code

answered Nov 17, 2015 at 0:15

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

1 Comment

The 21 Over a year ago

Thank for the lesson ,also i noticed other problem that was the order of parameter so i fixed write state first and then federalfilingstatus because the if was reverse order i wrote first state and federalfilistatus which the parameter were reverse.

Collectives™ on Stack Overflow

Create a user defined function?

3 Answers 3

2 Comments

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Related