How to break execution of Stored Procedures

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

General SQL Server Forums

New to SQL Server Programming

How to break execution of Stored Procedures

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2010-05-07 : 15:26:27

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
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 15:33:40

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.

yaman
Posting Yak Master

213 Posts

Posted - 2010-05-07 : 15:38:21

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

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 15:44:38

Sorry.. I misread your post.

yaman
Posting Yak Master

213 Posts

Posted - 2010-05-07 : 15:49:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources