none
Case tatement not working within a cursor

    Question

  • I am having a problem getting the case statement to work within a cursor in a store procedure in our 2008 r2 environment. Below is the cursor that I'm referring too. Any suggestions would be greatly appreciated.

    ------------------------------------------------------------------------------------------------

    SET NOCOUNT ON;
    DECLARE @part_id as int, @WAREHOUSE_ID AS varchar(80), @SAFETY_STOCK_QTY AS int;

    DECLARE parts_cursor CURSOR FOR
    select part_id ,WAREHOUSE_ID,  coalesce(SAFETY_STOCK_QTY,0) from PART_WAREHOUSE where Part_ID in (SELECT distinct  #FLINES03.Part from #FLINES03)


    OPEN parts_cursor
    FETCH NEXT FROM parts_cursor
    INTO @part_id, @WAREHOUSE_ID, @SAFETY_STOCK_QTY
    WHILE @@FETCH_STATUS = 0
    BEGIN

      CASE @WAREHOUSE_ID
        WHEN 'AAA' THEN UPDATE #FLINES03 SET WHS1_SS_QTY = @SAFETY_STOCK_QTY WHERE #FLINES03.PART = @part_id AND #FLINES03.WHS1 = @WAREHOUSE_ID
        WHEN 'BBB'THEN UPDATE #FLINES03 SET WHS2_SS_QTY = @SAFETY_STOCK_QTY WHERE #FLINES03.PART = @part_id AND #FLINES03.WHS2 = @WAREHOUSE_ID
        WHEN 'CCC'      THEN UPDATE #FLINES03 SET WHS3_SS_QTY = @SAFETY_STOCK_QTY WHERE #FLINES03.PART = @part_id AND #FLINES03.WHS3 = @WAREHOUSE_ID 
      END   

      FETCH NEXT FROM parts_cursor INTO @part_id, @WAREHOUSE_ID, @SAFETY_STOCK_QTY
    END
    CLOSE parts_cursor
    DEALLOCATE parts_cursor

    Tuesday, May 06, 2014 6:17 PM

Answers

  • CASE is an expression, not a statement. It means you can not execute code. You need to change your code to IF statements, e.g.

    IF @WAREHOUSE_ID = 'AAA'

        UPDATE ....

    IF 

        UPDATE ...

    ------------

    Also, what is the purpose of using cursor? Can you provide the whole picture of what you're trying to achieve?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 06, 2014 6:24 PM
    Moderator
  •   IF @WAREHOUSE_ID = 'AAA'
        UPDATE #FLINES03 
    	SET WHS1_SS_QTY = @SAFETY_STOCK_QTY 
    	WHERE #FLINES03.PART = @part_id 
    	  AND #FLINES03.WHS1 = @WAREHOUSE_ID
      IF @WAREHOUSE_ID = 'BBB'
        UPDATE #FLINES03 
    	SET WHS2_SS_QTY = @SAFETY_STOCK_QTY 
    	WHERE #FLINES03.PART = @part_id 
    	  AND #FLINES03.WHS2 = @WAREHOUSE_ID 
      IF @WAREHOUSE_ID = 'CCC'
        UPDATE #FLINES03 
    	SET WHS3_SS_QTY = @SAFETY_STOCK_QTY 
    	WHERE #FLINES03.PART = @part_id 
    	  AND #FLINES03.WHS3 = @WAREHOUSE_ID  
    


    sqldevelop.wordpress.com

    Tuesday, May 06, 2014 6:28 PM

All replies

  • CASE is an expression, not a statement. It means you can not execute code. You need to change your code to IF statements, e.g.

    IF @WAREHOUSE_ID = 'AAA'

        UPDATE ....

    IF 

        UPDATE ...

    ------------

    Also, what is the purpose of using cursor? Can you provide the whole picture of what you're trying to achieve?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 06, 2014 6:24 PM
    Moderator
  •   IF @WAREHOUSE_ID = 'AAA'
        UPDATE #FLINES03 
    	SET WHS1_SS_QTY = @SAFETY_STOCK_QTY 
    	WHERE #FLINES03.PART = @part_id 
    	  AND #FLINES03.WHS1 = @WAREHOUSE_ID
      IF @WAREHOUSE_ID = 'BBB'
        UPDATE #FLINES03 
    	SET WHS2_SS_QTY = @SAFETY_STOCK_QTY 
    	WHERE #FLINES03.PART = @part_id 
    	  AND #FLINES03.WHS2 = @WAREHOUSE_ID 
      IF @WAREHOUSE_ID = 'CCC'
        UPDATE #FLINES03 
    	SET WHS3_SS_QTY = @SAFETY_STOCK_QTY 
    	WHERE #FLINES03.PART = @part_id 
    	  AND #FLINES03.WHS3 = @WAREHOUSE_ID  
    


    sqldevelop.wordpress.com

    Tuesday, May 06, 2014 6:28 PM