This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
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
(
@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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
because @list is null before the loop. change your declaration for:
DECLARE @list varchar(1000) = ''
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.
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
one of the description fields is NULL?
SET @listTemp = isnull(@description, '') + ',' + isnull(@description2, '') + ',' + isnull(@description3, '')
Couple of potential issues...
The Date being passed should be enclosed in single quotes and ideally in the format yyyy-mm-dd ie:
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 :
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial