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
Open in new window