Use Cursor in a Scalar Function

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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