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?
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -