SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to break execution of Stored Procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yaman
Posting Yak Master

India
213 Posts

Posted - 05/07/2010 :  15:26:27  Show Profile  Reply with Quote
Sir ,

I want to break stored procedure when execution time of stored procedures is equal to 3 second .

Please help me out sir .

Yaman

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 05/07/2010 :  15:33:40  Show Profile  Reply with Quote
Declare a variable and increment it with 1 for inner execution.
Use RETURN keyword to exit after the variable count becomes 3.


Sample example:

CREATE PROCEDURE CountCheck
AS
BEGIN
Declare @iLoopCount Tinyint
Declare @iMaxCount Tinyint

Set @iLoopCount = 1
Set @iMaxCount = 3
While @iLoopCount <= 5 -- Greater than @iMaxCount
Begin
Print 'Count ' + Cast( @iLoopCount as varchar)

If @iLoopCount = @iMaxCount
Begin
Return
End
set @iLoopCount = @iLoopCount + 1
End
End


Regards,
Bohra


I am here to learn from Masters and help new bees in learning.
Go to Top of Page

yaman
Posting Yak Master

India
213 Posts

Posted - 05/07/2010 :  15:38:21  Show Profile  Reply with Quote
quote:
Originally posted by pk_bohra

Declare a variable and increment it with 1 for inner execution.
Use RETURN keyword to exit after the variable count becomes 3.


Sample example:

CREATE PROCEDURE CountCheck
AS
BEGIN
Declare @iLoopCount Tinyint
Declare @iMaxCount Tinyint

Set @iLoopCount = 1
Set @iMaxCount = 3
While @iLoopCount <= 5 -- Greater than @iMaxCount
Begin
Print 'Count ' + Cast( @iLoopCount as varchar)

If @iLoopCount = @iMaxCount
Begin
Return
End
set @iLoopCount = @iLoopCount + 1
End
End


Regards,
Bohra


I am here to learn from Masters and help new bees in learning.




This is my code , I am checking link server connectivity It take too much time . Now what i am doing , i am assuming if link server is connect then it takes only 3 second to check status else greater than 3 second .
Thats why we exec stored procedure only for 3 seconds .

This is my code (but it is not workin )


CREATE TABLE #TEMP_CONN(HOTEL VARCHAR(10), STATUS VARCHAR(10), ERROR VARCHAR(4000), [CONNECTION TIME] DATETIME)

DECLARE
@srvr nvarchar(128), @retval int, @retval2 varchar(200,@start_time DATETIME, @end_time DATETIME

set @srvr = 'DRECU';
begin try
SET @start_time = CURRENT_TIMESTAMP
exec @retval = sys.sp_testlinkedserver @srvr;
SET @end_time = CURRENT_TIMESTAMP
SELECT DATEDIFF(ss, @start_time, @end_time)

end try
begin catch
set @retval = sign(@@error);
set @retval2 = ERROR_MESSAGE()
end catch;

if @retval <> 0
begin

--2
INSERT INTO #TEMP_CONN VALUES('DRECU','FAILED',@retval2, GETDATE() )
end
if @retval = 0
begin
--2
INSERT INTO #TEMP_CONN VALUES('DRECU','SUCCESSFUL','SUCCESSFUL',GETDATE() )
select * From #TEMP_CONN
drop table #TEMP_CONN
end

Yaman
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 05/07/2010 :  15:44:38  Show Profile  Reply with Quote
Sorry.. I misread your post.

Go to Top of Page

yaman
Posting Yak Master

India
213 Posts

Posted - 05/07/2010 :  15:49:26  Show Profile  Reply with Quote
quote:
Originally posted by pk_bohra

Sorry.. I misread your post.





Sir , I want to stop execution of stored procedure if stored procedure execution time is going greater then three second .

Yaman
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000