troubleshooting Question

SQL Cursor Fails to Parse a 3 Column Query

Avatar of Bob
Bob asked on
Microsoft SQL Server 2008
9 Comments2 Solutions143 ViewsLast Modified:
In this app data is passed to and from the SQL server and parsed either in the DB to insert or the client for display.

The query passed to the Cursor is:

SELECT PropertyName, Property_ID, CrewChief FROM HTS.PropertyMaintenance WHERE DateOfActivity = 10-29-2018 ORDER BY  Document_ID ASC

It returns

Central Park             M3428       RC
Bryant Park              M83822      TJ
Prospect Park          B36482      WF

The string to built from this set:

'Central Park, M3428, RC, Bryant Park, M83822, TJ, Prospect Park, B36482, WF,'
(trailing comma is correct)

This SP returned a single column string successfully. When modified to return 3 column set it returns a null string? Can anyone see the error. I'm blind to it.
Thank you for any help.

CREATE PROCEDURE [HTS].[PropertyMaintenance_SELECT_DocumentsByDate]
(
      @QryByDate      varchar(300)
)
AS


declare @objcursor as cursor
 
declare
    @vsql       as nvarchar(max)
    ,@id        as int
    ,@value     as varchar(50)
   

DECLARE @list varchar(1000)
DECLARE @listTemp varchar(100)
DECLARE @description varchar(300)
DECLARE @description2 varchar(300)
DECLARE @description3 varchar(300)


set @vsql = 'set @cursor = cursor forward_only static for ' + @QryByDate + ' open @cursor; '
 
exec sys.sp_executesql
    @vsql
    ,N'@cursor cursor output'
    ,@objcursor output

fetch next from @objcursor into @description, @description2, @description3

while (@@fetch_status = 0)
begin

SET @listTemp =  @description + ',' + @description2 + ',' +  @description3

SET @list = concat(@list, @listTemp + ',')

fetch next from @objcursor into @description, @description2, @description3
end

close @objcursor
deallocate @objcursor

SELECT @list
ASKER CERTIFIED SOLUTION
Éric Moreau
Senior .Net Consultant
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros