MSSQL Cursor Return all rows into a single column

Question

I am attempting to return a list of all image names as a single result from an MSSQL CURSOR with the following stored procedure:

PROCEDURE [dbo].[GetPhotosByPropId]
    @propId bigint
AS
    DECLARE @AllPhotos nvarchar(MAX) 
    DECLARE @PhotoName nvarchar(500) 
    DECLARE @BaseURL nvarchar(200) 
    DECLARE @PhotosCursor CURSOR
    DECLARE @PhotosCount int


    -- Take Photo Count
    SET @PhotosCount = (SELECT Count(*) FROM [ART].[dbo].[STR_FK_PropertyPhoto] Where PropId = @propId)

    -- Take all photos in Cursor
    SET @PhotosCursor = CURSOR FAST_Forward For
     SELECT [Photo_File_Name],[Base_URL]
       FROM [ART].[dbo].[STR_FK_PropertyPhoto]
      WHERE PropId = @propId

    -- OPEN CURSOR for first record
    OPEN @PhotosCursor 
    FETCH NEXT FROM @PhotosCursor 
    INTO @PhotoName, @BaseURL             

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET @AllPhotos =  @AllPhotos + ',' +  @PhotoName                  
        FETCH NEXT FROM @PhotosCursor 
        INTO @PhotoName, @BaseURL
    END

    select @AllPhotos as "Img"

    CLOSE  @PhotosCursor 
    DEALLOCATE  @PhotosCursor 
    Go

The expected result is something like '1.jpg, 2.jpg, 3.jpg' is returned, but instead the outcome is NULL.

what version of SQL Server? You don't need a `CURSOR` to concatenate rows into a single line -- http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Taryn, Jun 30 '14 at 11:08

score 1 · Accepted Answer · answered Jun 30 '14 at 11:15

1

There are a lot of ways of doing this without using a cursor. But, if you insist on keeping your existing code, then initiate @AllPhotos first. Right now, it is NULL and whatever you add still equates to NULL

DECLARE @AllPhotos nvarchar(MAX) = ''

answered Jun 30 '14 at 11:15

Raj

10,653
2
45
52

score 0 · Answer 2 · answered Jun 30 '14 at 11:27

In the string concatenation SET @AllPhotos = @AllPhotos + ',' + @PhotoName, if any one of the Photo_File_Name entries from the cursor is NULL the end result will be NULL.

Changing your cursor query to the following will ensure that NULL values are returned as an empty string.

-- Take all photos in Cursor
SET @PhotosCursor = CURSOR FAST_Forward For
 SELECT COALESCE([Photo_File_Name], '') AS Photo_File_Name,
        [Base_URL]
   FROM [ART].[dbo].[STR_FK_PropertyPhoto]
  WHERE PropId = @propId

Note however that this will now return something like '1.jpg,2.jpg,,4.jpg' if, for example, the third Photo_File_Name was NULL.

This is easily remedied by wrapping the SET in an IF clause:

IF (@PhotoName <> '')
BEGIN
    SET @AllPhotos = @AllPhotos + ',' + @PhotoName
END

MSSQL Cursor Return all rows into a single column

2 Answers2