An Undeclared  and Invisible object is Referenced in Error Message

Bob
Bob used Ask the Experts™
on
An app is in development that tracts horticultural maintenance activity for NYC Department of Parks. That are many thousands of assets to track.

Data is moved to and from the DB through comma-delimited strings that are "manually" built on the fly.

To build these strings a dynamic cursor is used. Very slick when working, very annoying when not.

This SP uses such a cursor, but when run these errors are returned:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'F'.
Msg 16950, Level 16, State 2, Procedure TaskCompleted_SELECT_CompletedTasks_02, Line 23 [Batch Start Line 0]
The variable '@objcursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Procedure TaskCompleted_SELECT_CompletedTasks_02, Line 40 [Batch Start Line 0]
The variable '@objcursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Procedure TaskCompleted_SELECT_CompletedTasks_02, Line 41 [Batch Start Line 0]
The variable '@objcursor' does not currently have a cursor allocated to it.

Open in new window

"Incorrect syntax near 'F'" is baffling, since there is no text or object named "F" ? Figuring that "F" is unseen, I recreated the SP as a fresh doc and still it persists. Also are the '@objcursor'  a side erect  or a bug?

Any folks have a idea why this is? Thanks for any help.

ALTER PROCEDURE [HTS].[TaskCompleted_SELECT_CompletedTasks_02]
(
	@PropertyMaintenance_ID	as uniqueidentifier
)
AS

declare @objcursor as cursor 
 
declare 
    @vsql       as nvarchar(max)
    ,@vquery    as nvarchar(max)
    ,@id        as int
    ,@value     as varchar(50)
    
DECLARE @list varchar(1000)
DECLARE @listTemp varchar(100)
DECLARE @item varchar(100)

set @vquery = 'select Task_CD from HTS.TaskCompleted WHERE PropertyMaintenance_ID = ' +  
					CONVERT(varchar(36), @PropertyMaintenance_ID) + ' ORDER BY Task_CD ASC'

set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor; '
 
exec sys.sp_executesql
    @vsql
    ,N'@cursor cursor output'
    ,@objcursor output

fetch next from @objcursor into @item

while (@@fetch_status = 0)
begin

SET @listTemp =  @item

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

 fetch next from @objcursor into @item
end

close @objcursor
deallocate @objcursor
SELECT @list

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to escape the GUID in @vquery with quotes.
Right now the final @vsql would end up as:

WHERE PropertyMaintenance_ID = A5AAB4C8-A8D6-4184-B719-A4EEF0AFA0B5

And you're probably passing in a GUID with "F" in it, which is where it's failing.  You need that ID wrapped in single quotes so change to this:

set @vquery = 'select Task_CD from HTS.TaskCompleted WHERE PropertyMaintenance_ID = ''' +  
					CONVERT(varchar(36), @PropertyMaintenance_ID) + ''' ORDER BY Task_CD ASC'

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Just for kicks and giggles, double-click on the 'Incorrect syntax near F' error message, watch the cursor jump to the offending line, then tell us what line that is.
BobProgrammer

Author

Commented:
Thanks Guys
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Good solution. Well done :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial