| Author |
 Topic  |
|
|
yaman
Posting Yak Master
India
213 Posts |
 Posted - 05/07/2010 : 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
Flowing Fount of Yak Knowledge
India
1182 Posts |
Posted - 05/07/2010 : 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
India
213 Posts |
Posted - 05/07/2010 : 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
Flowing Fount of Yak Knowledge
India
1182 Posts |
Posted - 05/07/2010 : 15:44:38
|
Sorry.. I misread your post.
|
|
|
|
yaman
Posting Yak Master
India
213 Posts |
Posted - 05/07/2010 : 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 |
|
|
| |
Topic |
|
How to break execution of Stored Procedures
