CODEWITHSUNDEEP
sql-serversql-server-2008
Mathias F
Mathias F

Reputation: 15931

Handling Timeouts inside stored procedures

I have a stored procedure that executes a couple of queries. Each query might fail because of a timeout.

I still want to continue to execute the other queries. Is this possible? Does a try catch work for timeouts?

I can offcourse have different sps for each query, but that would make the application more complex.

Upvotes: 0

Views: 7349

Answers (4)

HLGEM
HLGEM

Reputation: 96640

If you are frequently getting timeouts, the fix is to performance tune the queries not to try to skip the timeouts and move onto the next query.

Timeouts are usually a sign of bad query design (Number 1 cause), bad database design (number 2 cause) or inadequate equipment. All three are fixable. So fix them.

Upvotes: 1

A-K
A-K

Reputation: 17090

Short answer: no. Timeout is just like when you cancel your query in SSMS: CATCH won't catch it. I wrote up a canned answer: Your TRY block may fail, and your CATCH block may be bypassed

Upvotes: 0

Will A
Will A

Reputation: 25008

The queries within Stored Procedures don't time out - the sql batch that is executing the sproc times out - so no, you can't 'catch' and then handle timeouts.

Upvotes: 1

TomTom
TomTom

Reputation: 62157

How you run the SQL?

SQL itself has no timeout, so what you describe as a problem is not possible.

The timeout is always handled on the connection level / connecting application. SQL Server is happy having SQL calls that last for hours or days.

So, unless you do sometihing "funny" / unusual the queries within the SP will not time out - the connection that calls the procedure will time out and thus rollback the transaction.

Upvotes: 5

Related Questions