? about using CASE in stored procedure

  • Thread starter Thread starter EManning
  • Start date Start date
E

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
 
EManning said:
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
Click to expand...

-----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-----
 
Back
Top