The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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, '')
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
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
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.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Inter… 277 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Inter… 218 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
Open in new window