locked
Use Cursor in a Scalar Function RRS feed

  • Question

  • ALTER FUNCTION [fnParseGarnish] 
    (
         @OrderId int
    )
    RETURNS varchar(100)
    AS
    EGIN
          -- Declare local variable here
          DECLARE @thisItem varchar(50)
          DECLARE @Result varchar(100) = CHAR(0)
    
          DECLARE garnish_cursor CURSOR LOCAL FORWARD_ONLY
              FOR
                SELECT Items.Item
                  FROM LunchOrderItems INNER JOIN Items
                    ON LunchOrderItems.ItemId = Items.ItemId
                 WHERE LunchOrderItems.LunchOrderId = @OrderId
                   AND Items.MenuTypeId = 3
     
          -- Open the cursor
          OPEN garnish_cursor
     
          -- ...go through each option and return the item description with comma
     
          FETCH NEXT FROM garnish_cursor INTO @thisItem
                 WHILE @@FETCH_STATUS = 0
                 BEGIN
                   SELECT @Result = @Result + @thisItem + ','
                   FETCH NEXT FROM garnish_cursor INTO @thisItem
     
                END
          -- Close the cursor
          CLOSE garnish_cursor
          DEALLOCATE garnish_cursor
          -- Trim the last comma from the string
          SELECT @Result = SUBSTRING(@Result, 1, LEN(@Result)-1)
           -- Return the result of the function
          RETURN @Result
     
    END
    

    The SQL here works in a Query window, but the function always returns a null string. Any help would be mos appreciated. Alternate methods would be fine, "I ask, teach."

    Cheers,

         John

    Friday, September 7, 2012 11:20 PM

Answers

  • Avoid cursors where possible, they will be slow.  Instead you can do

    ALTER FUNCTION [fnParseGarnish] 
    (
         @OrderId int
    )
    RETURNS varchar(100)
    AS
    BEGIN
          -- Declare local variable here
          DECLARE @thisItem varchar(50)
          DECLARE @Result varchar(100) = '' 
    
          ;WITH cte AS
          (SELECT (SELECT ',' + Items.Item
             FROM LunchOrderItems INNER JOIN Items
                    ON LunchOrderItems.ItemId = Items.ItemId
                 WHERE LunchOrderItems.LunchOrderId = @OrderId
                   AND Items.MenuTypeId = 3
             FOR XML PATH(''), Type) As Result)
          SELECT @Result = Result.value('.', 'varchar(100)') FROM cte;
    
          SELECT @Result = STUFF(@Result, 1, 1, '')
           -- Return the result of the function
          RETURN @Result
     
    END
    go

    Tom

    • Proposed as answer by Naomi N Sunday, September 9, 2012 1:21 AM
    • Marked as answer by oldamigauser Tuesday, November 6, 2012 9:46 PM
    Friday, September 7, 2012 11:49 PM
  • Answer was to change the bit in the fetch loop to account for the variable being NULL to start...

    BEGIN
        IF @Result Is NULL
            SELECT @Result = @thisItem + ','
        ELSE
            SELECT @Result = @Result + @thisItem + ','
        FETCH NEXT FROM garnish_cursor INTO @thisItem
    END
    
    If there is a better way to accomplish this, I would be glad to know of it.
    • Marked as answer by Iric Wen Monday, September 17, 2012 5:40 AM
    Friday, September 7, 2012 11:59 PM

All replies

  • Replace

    > DECLARE @Result varchar(100) = CHAR(0)

    with

    DECLARE @Result varchar(max) = '';

    Also make sure @thisItem is not null.



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Friday, September 7, 2012 11:35 PM
  • Avoid cursors where possible, they will be slow.  Instead you can do

    ALTER FUNCTION [fnParseGarnish] 
    (
         @OrderId int
    )
    RETURNS varchar(100)
    AS
    BEGIN
          -- Declare local variable here
          DECLARE @thisItem varchar(50)
          DECLARE @Result varchar(100) = '' 
    
          ;WITH cte AS
          (SELECT (SELECT ',' + Items.Item
             FROM LunchOrderItems INNER JOIN Items
                    ON LunchOrderItems.ItemId = Items.ItemId
                 WHERE LunchOrderItems.LunchOrderId = @OrderId
                   AND Items.MenuTypeId = 3
             FOR XML PATH(''), Type) As Result)
          SELECT @Result = Result.value('.', 'varchar(100)') FROM cte;
    
          SELECT @Result = STUFF(@Result, 1, 1, '')
           -- Return the result of the function
          RETURN @Result
     
    END
    go

    Tom

    • Proposed as answer by Naomi N Sunday, September 9, 2012 1:21 AM
    • Marked as answer by oldamigauser Tuesday, November 6, 2012 9:46 PM
    Friday, September 7, 2012 11:49 PM
  • Answer was to change the bit in the fetch loop to account for the variable being NULL to start...

    BEGIN
        IF @Result Is NULL
            SELECT @Result = @thisItem + ','
        ELSE
            SELECT @Result = @Result + @thisItem + ','
        FETCH NEXT FROM garnish_cursor INTO @thisItem
    END
    
    If there is a better way to accomplish this, I would be glad to know of it.
    • Marked as answer by Iric Wen Monday, September 17, 2012 5:40 AM
    Friday, September 7, 2012 11:59 PM
  • I gave you the solution above.

    Don't use CHAR(0) to initialize a string. Just set it to the empty string ('').

    Also, if the default setting is in force, concatening strings with any NULL will yield NULL. Best solution: use the ISNULL or COALESCE functions.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012




    • Edited by Kalman Toth Saturday, September 8, 2012 8:42 AM
    Saturday, September 8, 2012 8:36 AM