CASE help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE help

Author

Topic

intergalacticplanetary
Starting Member

12 Posts

Posted - 2007-03-11 : 19:17:54

hi, syntax question: i'm getting compile errors from this function (messages point to the CASE but it may be the preceding IF statement) and can't figure out what's wrong. I think I need some fresh eyes....if anyone can figure out what I'm doing wrong I would be indebted to you.

thanks and regards,

IP

CREATE FUNCTION hasSIC(@fac_id INT, @sic CHAR(4), @type INT)
RETURNS INT
AS
BEGIN
DECLARE @amount INT
SET @amount = 0

IF @fac_id IN (SELECT f.fac_id FROM fac_sic_dimension f JOIN sic_dimension s ON f.sic_id = s.sic_id WHERE s.sic_code = @sic)
BEGIN
CASE @type
WHEN 1 THEN SET @amount = (SELECT total_release FROM air_releases WHERE fac_id = @fac_id)
WHEN 2 THEN SET @amount = (SELECT total_release FROM land_releases WHERE fac_id = @fac_id)
WHEN 3 THEN SET @amount = (SELECT a_amount + b_amount + c_amount + d_amount + e_amount + f_amount FROM water_releases
WHERE fac_id = @fac_id)
END
END

RETURN @amount
END;

intergalacticplanetary
Starting Member

12 Posts

Posted - 2007-03-11 : 19:26:34

...never mind, got it :)

CREATE FUNCTION hasSIC(@fac_id INT, @sic CHAR(4), @type INT)
RETURNS INT
AS
BEGIN
DECLARE @amount INT
SET @amount = 0

IF @fac_id IN (SELECT f.fac_id FROM fac_sic_dimension f JOIN sic_dimension s ON f.sic_id = s.sic_id WHERE s.sic_code = @sic)
BEGIN
SELECT @amount =
CASE @type
WHEN 1 THEN (SELECT total_release FROM air_releases WHERE fac_id = @fac_id)
WHEN 2 THEN (SELECT total_release FROM land_releases WHERE fac_id = @fac_id)
WHEN 3 THEN (SELECT a_amount + b_amount + c_amount + d_amount + e_amount + f_amount FROM water_releases
WHERE fac_id = @fac_id)
END
END

RETURN @amount
END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources