? about using CASE in stored procedure

[EManning]

Using A2K adp, SQL 2000.

I have the following stored proc that I'd like to output a string. I keep
getting an error "Incorrect syntax near the keyword 'case'. Can someone
tell me what's wrong? Thanks.

Create Procedure spAbbreviation

@ToBeAbbreviated varchar(50),

@Abbreviation varchar(10) OUTPUT

As

case @ToBeAbbreviated

when 'Anesthesiology' then @Abbreviation = '(A)'

when 'Med/Peds' then@Abbreviation = '(M/P)'

end

 

[MGFoster]

Using A2K adp, SQL 2000.

I have the following stored proc that I'd like to output a string. I keep
getting an error "Incorrect syntax near the keyword 'case'. Can someone
tell me what's wrong? Thanks.

Create Procedure spAbbreviation
@ToBeAbbreviated varchar(50),
@Abbreviation varchar(10) OUTPUT
As

case @ToBeAbbreviated
when 'Anesthesiology' then @Abbreviation = '(A)'
when 'Med/Peds' then@Abbreviation = '(M/P)'
end

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use an If...Then structure instead of a Case structure. CASE
structures are usually used inside other structures (SELECT clause,
WHERE clause, GROUP BY clause, etc.). It can also be used in an
IF...THEN structure.

Your example would look like this when using IF...THEN

IF @ToBeAbbreviated = 'Anesthesiology'
SET @Abbreviation = '(A)'

IF @ToBeAbbreviated = 'Med/Peds'
SET @Abbreviation = '(M/P)'

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFD6h4echKqOuFEgEQILNgCeKtgiS7P+hfWiLkxcwamwgd8L51UAoIC1
W9x6z5kL0JyRzrlBRUM7WdaM
=7Wah
-----END PGP SIGNATURE-----