How to set timeout for a stored procedure in SQL Server

Question

I have a stored procedure in SQL Server, I want to set a time out for my sprpc so that if it does not finish in appropriate time, SQL Server forces it to stop.

thanks

Answer 1

Since you asked specifically about how to set the timeout value, I'll tell you, but I agree with the comment OMG Ponies made, that you are far better off if you figure out why the query is taking so long. To start reading about query optimization, read this and this.

Here are the steps to setting the Query Timeout on the server:

1. Locate your server in SQL Server Management Studio.
2. Right click on the server name.
3. Select properties.
4. Choose the Connections tab.
5. Set the query timeout value. (The value is in seconds. Set it to 0 for unlimited time)
6. Click OK.