Solved

Stored Procedure cursorFetch error on insert

Posted on 2010-09-02
6
346 Views
Last Modified: 2012-05-10
The follwoing is my stored procedure that grabs sql code sittng in tables to run.  What I did not figure in that if the sql code finds nothing I get the following error.

Msg 16924, Level 16, State 1, Line 42
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

I am guessing becaue the code found nothing and it wants to insert something, How can I change the Stored procedure to ignore this and keep going if it finds nothing.  Becaue finding nothing is good in this case.
declare @counter int, 

		@countfilter int, 

		@FilterID int, 

		@AssignedID int, 

		@UnitID int, 

		@DeployDate datetime,

		@TaskName varchar(60), 

		@Proponent varchar(60),

		@ApproveLev varchar(15),

		@Days int, 

		@Sql nvarchar(4000), 

		@PositionId int, 

		@Para varchar(4), 

		@Line varchar(3), 

		@Posn varchar(60), 

		@Sid varchar(60), 

		@Name varchar(60), 

		@SSN varchar(10), 

		@cur cursor, 

		@taskID int 

 

Declare Vend_Cursor CURSOR FOR Select uf.intFilterId, uf.intAssignedID, uf.intUnitMobId, ud.dtdeployDate,

										f.strTaskname, f.strProponent, f.strApprovalLevel, f.strSusDays, f.strSql 

										from tblSoldierFilters as uf Inner join tblUnitDeployData as ud on 

										ud.intUnitMobId = uf.intUnitMobId Inner Join tblFilter as f on 

										f.intFilterID = uf.intFilterId where uf.dtRemoved is NULL 

										and ud.intUnitMobId = 256 and f.intFilterID = 70

										order by uf.intfilterID, uf.intAssignedID, ud.intUnitMobId



OPEN Vend_Cursor



FETCH NEXT FROM Vend_Cursor INTO @FilterID,@AssignedId,@UnitId,@DeployDate,@Taskname,@Proponent,@ApproveLev,@Days,@Sql

 

while @@FETCH_STATUS =0 

begin

	--- Call the SQL Filter and assign the unit # as the variable to get at table	

	

	set @Sql = N'set @cur = cursor for ' + @Sql + Cast(@UnitID as varchar) + ';open @cur'



	exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output



	fetch next from @cur into @PositionId, @Para, @Line, @Posn, @Sid, @Name, @SSN

	

	while @@FETCH_STATUS =0 

	begin

		set @taskID = (select intTaskId from tblTask where intFilterId = @FilterID and intPositionId = @PositionId)

		

		if (select isnull(@taskID, 0)) = 0

		begin

			Insert Into tblTask (intAssignedId, intFilterId, intPositionID, strTaskName, dtFound, strStatus, 

						dtStatus, strSSN, strName, strPositionData, strSoldierData, strProponent, dtNotification,

						dtCompleteTask) 

			VALUES (@AssignedId, @FilterID, @PositionId, @Taskname, Getdate(), @ApproveLev, Getdate(),@SSN, 

					@Name, @Posn, @Sid, @Proponent, Getdate(), dateadd(DAY, -@Days, @DeployDate)) 

		end

		

		fetch next from @cur into @PositionId, @Para, @Line, @Posn, @Sid, @Name, @SSN

	end

	

	close @cur

	deallocate @cur	

	  

	FETCH NEXT FROM Vend_Cursor INTO @FilterID,@AssignedId,@UnitId,@DeployDate,@Taskname,@Proponent,@ApproveLev,@Days,@Sql

end



Close vend_Cursor

deallocate vend_Cursor

Open in new window

0
Comment
Question by:kdeutsch
  • 3
  • 3
6 Comments
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 500 total points
ID: 33589238
here is problem

set @Sql = N'set @cur = cursor for ' + @Sql + Cast(@UnitID as varchar) + ';open @cur'

        exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output

        fetch next from @cur into @PositionId, @Para, @Line, @Posn, @Sid, @Name, @SSN


check number of fields in select list and total number of into fields
0
 

Author Comment

by:kdeutsch
ID: 33589523
VjSoft:,

This has worked this way before,  I thought it was on the insert statement when it finds nothing.  I don't see how to check it, since I don't see what it is grabbing.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33589578
below line set @Sql = N'set @cur = cursor for ' + @Sql + Cast(@UnitID as varchar) + ';open @cur'

write

print @SQL

and see what is printing in Message window
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:kdeutsch
ID: 33589610
VjSoft:,

ok did a response.write on teh sql statement and yep that is the cause of the problem, I will have to get with the person to fix it.
THanks
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33589616
Great to hear :)
0
 

Author Closing Comment

by:kdeutsch
ID: 33589833
Thanks, I have been banging my head on this for days.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now