Help with function / Case statement 1

[sunmorgus]

I am having an issue getting a case statement to work withing a custom function that I am writing to add dashes to a SSN within my application. I have written the function as such:

USE HRDev
GO

CREATE FUNCTION dbo.fn_FixSSN (@SSN as char(11)) 
RETURNS char(11)
AS
BEGIN	
	CASE LEN(@SSN)
		WHEN 7 THEN SET @SSN = ('00' + Left(@SSN,1) + '-' + Substring(@SSN,2,2) + '-' +
							Right(@SSN,4))
		WHEN 8 THEN SET @SSN = ('0' + Left(@SSN,2) + '-' + Substring(@SSN,3,2) + '-' +
							Right(@SSN,4))
		ELSE SET @SSN = (Left(@SSN,3) + '-' + Substring(@SSN,4,2) + '-' +
							Right(@SSN,4))
	END
	RETURN @SSN
END

But when I go to execute the script and create the function, I get errors on the case statement that I cannot figure out:

[COLOR=red]
Msg 156, Level 15, State 1, Procedure fn_FixSSN, Line 6
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure fn_FixSSN, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure fn_FixSSN, Line 11
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure fn_FixSSN, Line 14
Incorrect syntax near the keyword 'RETURN'.
Msg 102, Level 15, State 1, Procedure fn_FixSSN, Line 15
Incorrect syntax near 'END'.
[/color]

The only thing that I can think of is that you cannot use a case statement outside of a select statement, but that would seem odd to me since you can use an If...Then construct outside of a select statement. I haven't tried writing the function using If...Then yet, as I would rather use the case statement, but I suppose if I have to I will. Am I right in thinking that I can't use the case statement? If not, what have I done wrong? Thanks in advance for any help!

 

[AlexCuse]

Try this for your CASE. You cannot use SET within your CASE statement.

select @SSN = CASE LEN(@SSN)
        WHEN 7 THEN ('00' + Left(@SSN,1) + '-' + Substring(@SSN,2,2) + '-' +
                            Right(@SSN,4))
        WHEN 8 THEN ('0' + Left(@SSN,2) + '-' + Substring(@SSN,3,2) + '-' +
                            Right(@SSN,4))
        ELSE (Left(@SSN,3) + '-' + Substring(@SSN,4,2) + '-' +
                            Right(@SSN,4))
    END

Ignorance of certain subjects is a great part of wisdom

 

[sunmorgus]

Thank you Alex, I tried that, and it seemed to help, it trimmed the error message down some, but I still get this:

Msg 156, Level 15, State 1, Procedure fn_FixSSN, Line 6
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Procedure fn_FixSSN, Line 15
Incorrect syntax near 'END'.

 

[Catadmin]

I was able to get it working with this:

BEGIN    
@Set SSN = (   SELECT CASE LEN(@SSN)
        WHEN 7 THEN ('00' + Left(@SSN,1) + '-' + Substring(@SSN,2,2) + '-' + Right(@SSN,4))
        WHEN 8 THEN ('0' + Left(@SSN,2) + '-' + Substring(@SSN,3,2) + '-' + Right(@SSN,4))
        ELSE (Left(@SSN,3) + '-' + Substring(@SSN,4,2) + '-' + Right(@SSN,4))
    END)
    RETURN @SSN
END

Of course, I did a PRINT @SSN instead of a Return. Also, something seems to be wrong with the concatenation as I was only able to return 7 characters when I set @SSN to a literal value of 123457. You might want to play with your code a bit to make sure that an entered value is getting padded appropriately.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

Can you post exactly what you tried to run? That query worked fine for me.

Another option you might try is this, which doesn't use CASE at all:

CREATE FUNCTION dbo.fn_FixSSN (@SSN varchar(11)) 
RETURNS char(11)
AS
BEGIN    

--add two leading zeroes and take the right 9 of resulting string    
set @SSN = right(replicate(0, 2) + convert(varchar(9), @ssn), 9)
--split your SSN with dashes
set @SSN = left(@SSN, 3) + '-' + substring(@SSN, 4, 2) + '-' + right(@SSN, 4)

return @SSN

END

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sunmorgus]

Catadmin, you were correct with your reply, I noticed it just before I read your reply, to use the Set statement before the Case, although, I didn't need to add the select at the beginning of the case. I am getting the same kind of weird output where it is only returning 7 characters, so I will have to look at that, but otherwise, thanks for your help!

 

[AlexCuse]

The returns are looking fine for me (on all 3 suggestions). Try changing your input to varchar(11).

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

Alex,

You're absolutely right. It's the CHAR that is the problem here. I just tested it myself.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

Hehe, CHAR is always a problem for me. Better yet would be varchar(9), as it does not appear that the user is passing in the dashes...



Ignorance of certain subjects is a great part of wisdom

 

[sunmorgus]

Yeah, I tried it and it works for me now. Thanks!

 

[Catadmin]

Glad we could help.

Alex, I starred you for the Varchar comment. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

A star from the reigning TMOW?!?!? Wow ;-)

Thanks Cat, didn't want you guys to bust your heads over it (data type used to always be the last thing I look at when trying to debug procs and functions, after many bad experiences it is now the first ). Nothing is more frustrating than trying everything you can think of, then when you are all out of ideas, it hits you.

Ah, the string is twenty eight characters and I am trying to fit it in a varchar(20)

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

Hee hee.

I love your icon banging its head against the wall. I feel like that today. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

it's not mine, it's tipmaster's. It's one of the more expressive ones to use:

[ignore]

[/ignore]

I feel like that for at least an hour every day. Must be the line of work...

Ignorance of certain subjects is a great part of wisdom