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
BobProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window

0
BobProgrammerAuthor Commented:
Thanks for the quick response, Eric.

I made the change with no success.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
BobProgrammerAuthor Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
one of the description fields is NULL?

SET @listTemp =  isnull(@description, '') + ',' + isnull(@description2, '') + ',' +  isnull(@description3, '')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Couple of potential issues...

The Date being passed should be enclosed in single quotes and ideally in the format yyyy-mm-dd ie:
SELECT PropertyName, Property_ID, CrewChief FROM HTS.PropertyMaintenance WHERE DateOfActivity = '2018-10-29' ORDER BY  Document_ID ASC

Open in new window


If that is the same search table, and given the dependency of the result params in the SP, then any merit in just passing DATE ?

And need to initialise, or concat the resulting @list correctly (as Eric suggested above).

If you can just pass date (and is the DateofActivity column a date or datetime ?) then you could do :
CREATE PROCEDURE [HTS].[PropertyMaintenance_SELECT_DocumentsByDate]
(
      @QryByDate      date
)
AS

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

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


declare objcursor cursor SELECT PropertyName, Property_ID, CrewChief 
                         FROM HTS.PropertyMaintenance 
                         WHERE DateOfActivity >= @QryByDate and DateOfActivity < dateadd(day,1,@QryByDate) 
                         ORDER BY  Document_ID ASC

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

while (@@fetch_status = 0)
begin

SET @listTemp =  isnull(@description,'') + ',' + isnull(@description2,'') + ',' +  isnull(@description3,'')

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

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

close @objcursor
deallocate @objcursor

SELECT @list

END

Open in new window

, and check the ISNULL() testing when concatenating strings, and building of @list
0
BobProgrammerAuthor Commented:
Much thanks to Eric and Mark.  Mark, extra thanks to you for the detailed answer.
0
Mark WillsTopic AdvisorCommented:
You're most welcome. Thanks for your comments. Happy to have been able to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.