December 17, 2015 at 6:38 am
Hi,
Below is the scenario.
CREATE PROCEDURE SP_MAIN
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
EXEC SP_CHILD_1
EXEC SP_CHILD_2
END
GO
In the above code if either of the inner stored procedure takes more than a specified time (I want to pass that as parameter) to complete then I want to quit the execution of main stored procedure (SP_MAIN).
Please advice this can be done through t-sql
Prior to this both the inner stored procedures were called individually and I was able to control there execution through command time out using ADO.NET from application side
Please let me know if this can be handled from tsql
Thanks
Sam
December 17, 2015 at 6:39 am
No, it can't. You'd do that with a client timeout, though the timeout affects the entire batch sent to the server, not subprocedures.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2015 at 7:01 am
Using a technique similar to what I've done below, you can exit the main procedure should the elapsed runtime of a sub-procedure call exceed a specific duration. You'll want the calling application to check the execution return code to determine whether all steps completed or if it exited incomplete.
However, explain a little more about why you want to do this, and then we can perhaps suggest a better solution.
CREATE PROCEDURE SP_MAIN
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @RunTime DATETIME;
SELECT @RunTime = GETDATE();
EXEC SP_CHILD_1;
IF DATEDIFF( second, @RunTime, GETDATE() ) > 10
BEGIN;
RETURN -1;
END;
SELECT @RunTime = GETDATE();
EXEC SP_CHILD_2;
IF DATEDIFF( second, @RunTime, GETDATE() ) > 10
BEGIN;
RETURN -2;
END;
RETURN 0;
END
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2015 at 2:59 pm
It depends. It won't happen automatically. But if you can add code to SP_CHILD_1 and SP_CHILD_2 to end after that period of time, you could have the child proc send a specific return code, say -10, that "told" the calling proc that it had timed out. At that point, the calling proc could do whatever it needed to do based on that return code.
Of course you'd have to adjust the child exec's in the main proc to accept back the return code:
EXEC @return_code = SP_CHILD_1
IF @return_code = -10 BEGIN ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 18, 2015 at 12:25 am
Hi Eric,
Your solution will work however I have one query . Can I handle lock/deadlock issue with this approach. For instance if in any of the sub-procedures there is a table getting used in it and is locked by some other transaction then in that case the subprocedure will wait till its get released and once the control comes to Main procedure we can check the time taken and accordingly exit .
So is there way to check if there are any existing lock on the table used in subprocedure and then handle it differently. Is it advisable to do this check all the time
However, explain a little more about why you want to do this, and then we can perhaps suggest a better solution.
Before this Main procedure we used to call the sub procedures separately (and there by controlling the execution thru command time out option of ADO.NET)from application for each transaction in a loop and which was taking lot of time considering the no of records that we were processing per day.Business wanted to have all the logic embedded in a single object and do the processing.
December 21, 2015 at 12:07 am
Stored procedure calls without EXEC must be the first command in a command batch. Even if this were the case initially, inserting additional lines before the procedure call at some point in the future would break your code.
You should also be sure to owner-qualify procedure calls. Omitting the owner from a procedure call causes SQL Server to momentarily place a compile lock on the procedure because it cannot locate it immediately in the procedure cache. This lock is released once the procedure-sans-owner is located in the cache, but can still cause problems in high-throughput environments. Owner-qualifying objects is simply a good habit to get into. It's one of those things you can do to save yourself problems down the road. if you want to more information to visit here[/url] it will give the many information about this stored procedure.
December 21, 2015 at 10:38 am
sam 55243 (12/18/2015)
Hi Eric,Your solution will work however I have one query . Can I handle lock/deadlock issue with this approach. For instance if in any of the sub-procedures there is a table getting used in it and is locked by some other transaction then in that case the subprocedure will wait till its get released and once the control comes to Main procedure we can check the time taken and accordingly exit .
So is there way to check if there are any existing lock on the table used in subprocedure and then handle it differently. Is it advisable to do this check all the time
However, explain a little more about why you want to do this, and then we can perhaps suggest a better solution.
Before this Main procedure we used to call the sub procedures separately (and there by controlling the execution thru command time out option of ADO.NET)from application for each transaction in a loop and which was taking lot of time considering the no of records that we were processing per day.Business wanted to have all the logic embedded in a single object and do the processing.
If another transaction is holding an exclusive lock on a table for an extended period of time (ex: more than a few seconds), then optimizing that process so it doesn't block others should generally be the focus of your efforts, not coding a work around for other processes trying to read from the table.
However, you may want to read into the NOWAIT query hint or the LOCK_TIMEOUT session setting, which sets a limit on the number of milliseconds that a query will wait for a blocking process to complete. Understand that if the timeout is exceeded, then an error is thrown, which will need to be handled in code or raised to the application.
Query Hint NOWAIT – How to Not Wait on Locked Query
SET LOCK_TIMEOUT – How to Not Wait on Locked Query
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 22, 2016 at 8:40 am
Thank you for the post. Here you can check out assignment help at handmadewritings.com Sure you will enjoy the website!
March 31, 2017 at 9:39 am
This was removed by the editor as SPAM
April 2, 2017 at 5:36 pm
sam 55243 - Thursday, December 17, 2015 6:38 AMHi, Below is the scenario.CREATE PROCEDURE SP_MAINASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; EXEC SP_CHILD_1 EXEC SP_CHILD_2 ENDGOIn the above code if either of the inner stored procedure takes more than a specified time (I want to pass that as parameter) to complete then I want to quit the execution of main stored procedure (SP_MAIN).Please advice this can be done through t-sqlPrior to this both the inner stored procedures were called individually and I was able to control there execution through command time out using ADO.NET from application sidePlease let me know if this can be handled from tsqlThanksSam
It's far easier to write faster code than it is to measure and rollback slow code. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2018 at 1:33 am
This was removed by the editor as SPAM
November 1, 2022 at 12:04 pm
This was removed by the editor as SPAM
November 1, 2022 at 12:06 pm
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply