Avatar of Bob
Bob
 asked on

SQL Cursor Fails to Parse a 3 Column Query

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
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
Éric Moreau

because @list is null before the loop. change your declaration for:
DECLARE @list varchar(1000) = ''

Open in new window

Bob

ASKER
Thanks for the quick response, Eric.

I made the change with no success.
Jim Horn

Just for kicks and giggles, write this line above your bottom Fetch Next, run it, and tell us what displays.
SELECT @list as list, @listTemp as listTemp, @description as description, @description2 as description2, @description3 as description3

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Horn

Also what's the purpose of using dynamic T-SQL for @vsql using a cursor?  
This seems like an excellent way to spend a ton of time troubleshooting.
Bob

ASKER
Hope you get a kick out of this, Jim. It displays:  NULL      NULL      NULL      NULL      NULL, etc.

As far as using dynamic SQL for @sql that is where my misadventures led me. If you know a better way to do this please let know. I'm always looking to reduce ignorance and improve my SQL skills.

TY
ASKER CERTIFIED SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Bob

ASKER
Much thanks to Eric and Mark.  Mark, extra thanks to you for the detailed answer.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Wills

You're most welcome. Thanks for your comments. Happy to have been able to help :)