CASE in SP - 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 in SP

Author

Topic

Tuppers
Starting Member

12 Posts

Posted - 2005-11-26 : 07:34:26

hi i didnt know SQL server could use case statements to assign a string that can be returned so it can be displayed. I have the following SP:

CREATE PROCEDURE GetProductsInCategory
(@CategoryID int)
AS

SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,
Product.OnDepartmentPromotion, Product.OnCatalogPromotion, Product.ImageALT,
Product.Stock, Artist.[ArtistName], AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID

FROM (Product INNER JOIN
(Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID=AlbumSingleDetails.ArtistID)
ON Product.ProductID=AlbumSingleDetails.ProductID)
INNER JOIN ProductCategory ON Product.ProductID=ProductCategory.ProductID
WHERE ProductCategory.CategoryID=@CategoryID
ORDER BY Artist.[ArtistName], Product.Name

RETURN

not sure how to add the CASE ini have tried the following:
SQLCoverQuality = CASE AlbumSingleDetails.CoverQualityID
WHEN AlbumSingleDetails.CoverQualityID = 1 THEN 'MINT NEW'
WHEN AlbumSingleDetails.CoverQualityID = 2 THEN 'Excellent'
WHEN AlbumSingleDetails.CoverQualityID = 3 THEN 'Very Good'
WHEN AlbumSingleDetails.CoverQualityID = 4 THEN 'Good'
ELSE 'Unknown'
END
SQLQuality = CASE AlbumSingleDetails.QualityID
WHEN AlbumSingleDetails.QualityID = 1 THEN 'MINT NEW'
WHEN AlbumSingleDetails.QualityID = 2 THEN 'Excellent'
WHEN AlbumSingleDetails.QualityID = 3 THEN 'Very Good'
WHEN AlbumSingleDetails.QualityID = 4 THEN 'Good'
ELSE 'Unknown'
END

i get a syntax error re the CASE and = if i remove it!!! any ideas? also would i not have to declare the two fields i have created? SQLQuality? and SQLCoverQuality?

Can anyone help?

Tuppers

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-26 : 07:55:05

Your example incorrectly combines the "simple case" and "searched case" syntaxes.

From BOL:

Syntax
Simple CASE function:

CASE input_expression 
    WHEN when_expression THEN result_expression 
        [ ...n ] 
    [ 
        ELSE else_result_expression 
    ] 
END 

Searched CASE function:

CASE
    WHEN Boolean_expression THEN result_expression 
        [ ...n ] 
    [ 
        ELSE else_result_expression 
    ] 
END

To correct your examples, just remove the input_expression and go with the searched CASE syntax.

Tuppers
Starting Member

12 Posts

Posted - 2005-11-26 : 10:43:56

thanks for that! i have now got the following code:
CREATE PROCEDURE GetProductsInCategory
(@CategoryID int)
AS

SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,
Product.OnDepartmentPromotion, Product.OnCatalogPromotion, Product.ImageALT,
Product.Stock, Artist.[ArtistName],
CASE AlbumSingleDetails.CoverQualityID
WHEN 1 THEN 'MINT NEW'
WHEN 2 THEN 'Excellent'
WHEN 3 THEN 'Very Good'
WHEN 4 THEN 'Good'
ELSE 'Unknown'
END,
CASE AlbumSingleDetails.QualityID
WHEN 1 THEN 'MINT NEW'
WHEN 2 THEN 'Excellent'
WHEN 3 THEN 'Very Good'
WHEN 4 THEN 'Good'
ELSE 'Unknown'
END

FROM (Product INNER JOIN
(Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID=AlbumSingleDetails.ArtistID)
ON Product.ProductID=AlbumSingleDetails.ProductID)
INNER JOIN ProductCategory ON Product.ProductID=ProductCategory.ProductID
WHERE ProductCategory.CategoryID=@CategoryID
ORDER BY Artist.[ArtistName], Product.Name

RETURN

this compiles but i cannot use the QualityID or CoverQualityID to display on the website? have i missed something?

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 11:52:37

Expressions must be explicitly named.


 CASE AlbumSingleDetails.CoverQualityID
WHEN 1 THEN 'MINT NEW'
WHEN 2 THEN 'Excellent'
WHEN 3 THEN 'Very Good'
WHEN 4 THEN 'Good'
ELSE 'Unknown'
END as nameOfYourChoice,

Tuppers
Starting Member

12 Posts

Posted - 2005-11-26 : 12:50:46

thanks worked a treat!! Cheers for all the help!!

Tuppers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources