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_Do cumentsByD ate]
(
@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
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
(
@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
Thanks for the quick response, Eric.
I made the change with no success.
I made the change with no success.
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
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.
This seems like an excellent way to spend a ton of time troubleshooting.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Much thanks to Eric and Mark. Mark, extra thanks to you for the detailed answer.
You're most welcome. Thanks for your comments. Happy to have been able to help :)
Open in new window