Microsoft Sql Cursor Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack error handling in cursor sql server Overflow the company Business Learn more about hiring developers or posting ads with us t sql cursor continue Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community try catch cursor sql server of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to continue cursor loop even error occured in the loop up vote 3 down vote sql server cursor continue on error favorite I want to insert rows of one table into another. For that I have the below procedure. ALTER PROCEDURE LOADDATA_a AS BEGIN TRUNCATE TABLE STIDENT_A DECLARE @SID INT DECLARE @SNAME VARCHAR(50) DECLARE @SUB VARCHAR(50) DECLARE @MARKS INT DECLARE LOAD_DATA CURSOR FAST_FORWARD FOR SELECT SID,SNAME,SUB,MARKS FROM student OPEN LOAD_DATA FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRANSACTION INSERT INTO STIDENT_A(SID,SNAME,SUB,MARKS) VALUES (@SID,@SNAME,@SUB,@MARKS) IF @@ERROR != 0
Continue Sql
BEGIN ROLLBACK TRANSACTION RETURN END ELSE BEGIN COMMIT TRANSACTION END FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS END CLOSE LOAD_DATA DEALLOCATE LOAD_DATA END Whenever a row fails to insert, the cursor fails and stops there, but I need it to continue. How to do that? sql-server tsql share|improve this question edited Apr 9 '13 at 5:44 marc_s 454k938701032 asked Apr 9 '13 at 5:08 user2018408 2912 2 I think cursors are evil and should never have existed in tsql... –Chris Moutray Apr 9 '13 at 5:42 3 @ChrisMoutray: I think you are being a bit too dismissive. There are situations where cursors are helpful. Otherwise they would probably not have been introduced. –Andriy M Apr 9 '13 at 5:44 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote Apparently, you've got a RETURN statement immediately after ROLLBACK TRANSACTION. Have you tried just removing it? Although you could also rewrite the body using TRY/CATCH instead, like this: ... WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY INSERT INTO STIDENT_A(SID,SNAME,SUB,MARKS) VALUES (@SID,@SNAME,@SUB,@MARKS); END TRY BEGIN CATCH -- this section must have some statement, -- so, why not log the erroneous data to the screen at least? PRINT @SID; PRINT @SNAME; PRINT @SUB; PRINT @MARKS; PRINT ''; -- an emp
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On
Cursor_status
Home » SQL Server 2005 » T-SQL (SS2K5) » Try/Catch in Cursor sql continue while loop It Breaks after failure Try/Catch in Cursor It Breaks after failure Rate Topic Display Mode Topic Options select 'oracle' from dual where null = null; Author Message GhantaGhanta Posted Monday, August 10, 2009 6:17 AM SSC Veteran Group: General Forum Members Last Login: Tuesday, January 3, 2012 11:53 AM Points: 231, Visits: 698 Hey http://stackoverflow.com/questions/15893741/how-to-continue-cursor-loop-even-error-occured-in-the-loop GUys,I do not need the cursor to break after failures and want to iterate through all the records... Any suggestion how can I change this code below? Thanks!BEGIN TRY DECLARE test_cursor CURSOR STATIC FOR SELECT [file_name] , op_format FROM ABC..test --- THIS IS FOR TESTING PURPOSE!!!!!!!!! OPEN test_cursor FETCH NEXT FROM test_cursor INTO @var1, @var2 WHILE @@FETCH_STATUS http://www.sqlservercentral.com/Forums/Topic767778-338-1.aspx = 0 BEGIN <<<
Microsoft Tech Companion App Microsoft Technical Communities Microsoft Virtual Academy Script Center Server and Tools Blogs TechNet Blogs TechNet https://technet.microsoft.com/en-us/library/ms190385(v=sql.105).aspx Flash Newsletter TechNet Gallery TechNet Library TechNet Magazine TechNet Subscriptions TechNet Video TechNet Wiki Windows Sysinternals Virtual Labs Solutions Networking Cloud and Datacenter Security Virtualization Downloads Updates Service Packs Security Bulletins Windows Update Trials Windows Server 2016 System Center 2016 Windows 10 Enterprise SQL Server 2016 See all trials » Related Sites Microsoft sql server Download Center TechNet Evaluation Center Drivers Windows Sysinternals TechNet Gallery Training Training Expert-led, virtual classes Training Catalog Class Locator Microsoft Virtual Academy Free Windows Server 2012 courses Free Windows 8 courses SQL Server training Microsoft Official Courses On-Demand Certifications Certification overview MCSA: Windows 10 Windows Server Certification (MCSE) Private Cloud Certification (MCSE) SQL cursor sql server Server Certification (MCSE) Other resources TechNet Events Second shot for certification Born To Learn blog Find technical communities in your area Support Support options For business For developers For IT professionals For technical support Support offerings More support Microsoft Premier Online TechNet Forums MSDN Forums Security Bulletins & Advisories Not an IT pro? Microsoft Customer Support Microsoft Community Forums United States (English) Sign in Home Library Wiki Learn Gallery Downloads Support Forums Blogs We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Accessing and Changing Database Data Procedural Transact-SQL Control-of-Flow Control-of-Flow Using WHILE...BREAK or CONTINUE Using WHILE...BREAK or CONTINUE Using WHILE...BREAK or CONTINUE Using BEGIN...END Using GOTO Using IF...ELSE Using RETURN Using WAITFOR Using WHILE...BREAK or CONTINUE Using CASE TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived and is not being maintained. Using