[RESOLVED] Case statements problem in SP

[FishGuy]

I get an incorrect syntax error in this SP

Incorrect syntax near key word case.

I think I may need to parenthasize somewhere but cant figure it out and my deadline is approaching fast, any help please.

ALTER PROCEDURE dbo.sp_GetAvailableStockInLocationByType
 
    (
        @Location integer,
        @PK_StockTypeID integer
    )
 
AS
    /* SET NOCOUNT ON */
    
    select case @PK_StockTypeID
    case  1 
     SELECT     StockItems.PK_StockItemID, CarriageStock.StockSerialNumber
           FROM         CarriageStock INNER JOIN
                                 StockItems ON CarriageStock.FK_StockItemID = StockItems.PK_StockItemID AND CarriageStock.FK_StockTypeID = StockItems.FK_StockTypeID
           WHERE     (CarriageStock.FK_StockTypeID = @PK_StockTypeID) AND (StockItems.FK_ShippingStatus = 3) AND (StockItems.FK_CurrentSiteID = @Location)
    
    case  2
    SELECT     StockItems.PK_StockItemID, RailStock.StockSerialNumber
    FROM         RailStock INNER JOIN
                          StockItems ON RailStock.FK_StockItemID = StockItems.PK_StockItemID AND RailStock.FK_StockTypeID = StockItems.FK_StockTypeID
    WHERE     (StockItems.FK_ShippingStatus = 3) AND (StockItems.FK_StockTypeID = @PK_StockTypeID) AND (StockItems.FK_CurrentSiteID = @Location)
 
    case  3
    SELECT    StockItems.PK_StockItemID, PlatformStock.StockSerialNumber
    FROM         PlatformStock INNER JOIN
                          StockItems ON PlatformStock.FK_StockItemID = StockItems.PK_StockItemID AND PlatformStock.FK_StockTypeID = StockItems.FK_StockTypeID
    WHERE     (StockItems.FK_ShippingStatus = 3) AND (StockItems.FK_StockTypeID = @PK_StockTypeID) AND (StockItems.FK_CurrentSiteID = @Location)
 
    end 
 
    RETURN

[szlamany]

Select Case @XYZ When 1 Then 'This'
       When 2 Then 'That'
       Else 'Nothing' End

...or...

Select Case When @XYZ=1 Then 'This'
           Case When @XYZ=2 Then 'That'
            Else 'Nothing' End

Both syntax work...

The second syntax can have AND and OR's in each CASE so it's more powerful.

The ELSE is always good to have - otherwise it can return a NULL!

[FishGuy]

changed

ALTER PROCEDURE dbo.sp_GetAvailableStockInLocationByType
 
    (
        @Location integer,
        @PK_StockTypeID integer
    )
 
AS
    /* SET NOCOUNT ON */
    
    if @PK_StockTypeID = 1
    begin
    
     SELECT     StockItems.PK_StockItemID, CarriageStock.StockSerialNumber
           FROM         CarriageStock INNER JOIN
                                 StockItems ON CarriageStock.FK_StockItemID = StockItems.PK_StockItemID AND CarriageStock.FK_StockTypeID = StockItems.FK_StockTypeID
           WHERE     (CarriageStock.FK_StockTypeID = @PK_StockTypeID) AND (StockItems.FK_ShippingStatus = 3) AND (StockItems.FK_CurrentSiteID = @Location)
    end
    if @PK_StockTypeID = 2
    begin
    
    SELECT     StockItems.PK_StockItemID, RailStock.StockSerialNumber
    FROM         RailStock INNER JOIN
                          StockItems ON RailStock.FK_StockItemID = StockItems.PK_StockItemID AND RailStock.FK_StockTypeID = StockItems.FK_StockTypeID
    WHERE     (StockItems.FK_ShippingStatus = 3) AND (StockItems.FK_StockTypeID = @PK_StockTypeID) AND (StockItems.FK_CurrentSiteID = @Location)
 
    end
    if @PK_StockTypeID = 3
    begin
    
    SELECT    StockItems.PK_StockItemID, PlatformStock.StockSerialNumber
    FROM         PlatformStock INNER JOIN
                          StockItems ON PlatformStock.FK_StockItemID = StockItems.PK_StockItemID AND PlatformStock.FK_StockTypeID = StockItems.FK_StockTypeID
    WHERE     (StockItems.FK_ShippingStatus = 3) AND (StockItems.FK_StockTypeID = @PK_StockTypeID) AND (StockItems.FK_CurrentSiteID = @Location)
 
    end 
 
    RETURN

[szlamany]

and yes - it cannot be used like an IF - it's for being buried in a SELECT statement...