where to put error handling in cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

where to put error handling in cursor?

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 09:56:30

I have a cursor:


Fetch NEXT 
	FROM MY_CURSOR 
	INTO	@ServerName
			
	While (@@FETCH_STATUS <> -1)

	BEGIN

		EXEC ZZ_execRemoteProcedure @ServerName,'ZZ_TicketTransactions',@Parameters,'ZZ_rptTicketsSummary','ZZ_rptTicketsSummary'

		Fetch NEXT 
		FROM MY_CURSOR 
		INTO	@ServerName

	END

	CLOSE MY_CURSOR
	DEALLOCATE MY_CURSOR

I am using an error handling outside. Will this work or i need to put it inside the cursor?


IF (@@ERROR <> 0)
	BEGIN
		DECLARE @MYERR AS NVARCHAR(MAX)
		
		SELECT @MYERR = ERROR_MESSAGE()
		RAISERROR(@MYERR, 20, 1)
		RETURN -1
	END

Note the cursor is calling another stored procedure and i want to try and catch any server timeout problem.
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-21 : 10:10:21

Why not wrap the 'EXEC ZZ...' in a TRY/CATCH?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 10:46:47

Hi.
Yes but i am afraid any close cursor issues with this one.
I am using this one: Is it ok?


BEGIN TRY
	DECLARE MY_CURSOR Cursor
	FOR 
		SELECT	DISTINCT Cinema_strServerName
		--SELECT	DISTINCT REPLACE(REPLACE(Cinema_strServerName,'SSRV2','COSQL') ,'SRV','SQL')
		From	tblCinema C JOIN tblCinema_Operator CO ON C.Cinema_strCode = CO.Cinema_strCode
		WHERE	C.Cinema_strCode = CASE
									WHEN
										@Cinema = 'CI'
									THEN
										C.Cinema_strCode
									ELSE
										@Cinema
									END
				AND CinOperator_strOnline = 'Y'
	Open MY_CURSOR 


	Fetch NEXT 
	FROM MY_CURSOR 
	INTO	@ServerName
			
	While (@@FETCH_STATUS <> -1)

	BEGIN

		EXEC ZZ_execRemoteProcedure @ServerName,'ZZ_TicketTransactions',@Parameters,'ZZ_rptTicketsSummary','ZZ_rptTicketsSummary'

		Fetch NEXT 
		FROM MY_CURSOR 
		INTO	@ServerName

	END

	CLOSE MY_CURSOR
	DEALLOCATE MY_CURSOR
	END TRY
	BEGIN CATCH

	CLOSE MY_CURSOR
	DEALLOCATE MY_CURSOR

	RETURN -1
	END CATCH

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-21 : 11:49:37

Should work, but you'll stop on the first timeout. Is that what you want?

BTW, how big is the result set from the cursor? If not too big, you might want to consider other ways to do it and eliminate the cursor.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 16:58:46

Yeah, i would like to stop at the first timeout.
The result set is not so big, i haven't written the cursor so maybe this should probably change but seems ok for calling an bunch exec's . To tell you the truth i think this one only gives out one result so i cursor is not needed but since i am not fully familiar with the exact usage of some sprocs(maybe used elsewhere) i prefer to be on the safe side for now.
Would be interesting to see how would i call exec's in a more efficient way though. If you have an idea(?)
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 09:32:36

if not too many rows in the cursor, here are two alternatives:

1. use the query in the define cursor command to write to a table variable instead. Give the table variable an identity(1,1) column as PK. Then iterate through the the table in a while loop, selecting by the identity value


declare @i int = 1
declare @n int = (select count(*) from @table)

while @i <= @n begin
    select @servername = servername from @table 
    where id = @i
    set @i += 1
    exec ....
end

2. build up the exec calls as a big NVARCHAR then use sp_executesql to run it.



declare @sql nvarchar(4000) = (
    select ';' + <rest of your query>
    for xml path ('')
)

exec sp_executesql @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources