QUERY - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

QUERY

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-05 : 02:37:46

Hi
I have procedures like this.



CREATE PROC TEST1
AS
	EXEC TEST2



CREATE PROC TEST2
AS
BEGIN
DECLARE @MODEL INT
DECLARE @i INT

		WHILE @i<= 10
		
			IF	@MODEL	=	1
					EXEC TEST3	1
			IF	@MODEL	=	2
					EXEC TEST4	2
			IF	@MODEL	=	3
					EXEC TEST5	3
			IF	@MODEL	=	4
					EXEC TEST6	4
			IF	@MODEL	=	5
					EXEC TEST7	5
			IF	@MODEL	=	6
					EXEC TEST8	6
			IF	@MODEL	=	7
					EXEC TEST9	7
		SET 	@i	=	@i + 1
		END
END

If I run this procedure TEST1 it will call inner proc Test2
So the test2 proc will call 7 inner procedures in while loop.

I want to create error handling of this scenario.

Scope
If I get any error any of the procedures it will catch the error message and save it in table but the loop will continue to next proc…

How to handle this. Help on this...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 03:00:40

[code]CREATE PROC TEST1
AS

SET NOCOUNT ON
EXEC TEST2
GO

CREATE PROC TEST2
AS

SET NOCOUNT ON

DECLARE @MODEL INT,
@i INT,
@Errors INT

SELECT @i = 1,
@Errors = 0

WHILE @i <= 10
BEGIN
IF @MODEL = 1
BEGIN
EXEC TEST3 @MODEL
SET @Errors = @Errors + SIGN(ABS(@@ERROR))
END

IF @MODEL = 2
BEGIN
EXEC TEST4 @MODEL
SET @Errors = @Errors + SIGN(ABS(@@ERROR))
END

IF @MODEL = 3
BEGIN
EXEC TEST5 @MODEL
SET @Errors = @Errors + SIGN(ABS(@@ERROR))
END

IF @MODEL = 4
BEGIN
EXEC TEST6 @MODEL
SET @Errors = @Errors + SIGN(ABS(@@ERROR))
END

IF @MODEL = 5
BEGIN
EXEC TEST7 @MODEL
SET @Errors = @Errors + SIGN(ABS(@@ERROR))
END

IF @MODEL = 6
BEGIN
EXEC TEST8 @MODEL
SET @Errors = @Errors + SIGN(ABS(@@ERROR))
END

IF @MODEL = 7
BEGIN
EXEC TEST9 @MODEL
SET @Errors = @Errors + SIGN(ABS(@@ERROR))
END

SET @i = @i + 1
END

SELECT @Errors[/code]

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-05 : 03:09:07

Hi

thanks peso..

Am not understand why you used SIGN and ABS please explain me..
@@ERROR will get only error number right..
i need error message also..how can i rewrite this...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 03:23:57

[code]CREATE PROC TEST1
AS

SET NOCOUNT ON
EXEC TEST2
GO

CREATE PROC TEST2
AS

SET NOCOUNT ON

DECLARE @MODEL INT,
@i INT

DECLARE @Errors TABLE
(
Description VARCHAR(1000)
)

SELECT @i = 1,
@Errors = 0

WHILE @i <= 10
BEGIN
IF @MODEL = 1
BEGIN
EXEC TEST3 @MODEL

INSERT @Errors
SELECT ERROR_MESSAGE()
WHERE ERROR_NUMBER() <> 0
END

IF @MODEL = 2
BEGIN
EXEC TEST4 @MODEL

INSERT @Errors
SELECT ERROR_MESSAGE()
WHERE ERROR_NUMBER() <> 0
END

IF @MODEL = 3
BEGIN
EXEC TEST5 @MODEL

INSERT @Errors
SELECT ERROR_MESSAGE()
WHERE ERROR_NUMBER() <> 0
END

IF @MODEL = 4
BEGIN
EXEC TEST6 @MODEL

INSERT @Errors
SELECT ERROR_MESSAGE()
WHERE ERROR_NUMBER() <> 0
END

IF @MODEL = 5
BEGIN
EXEC TEST7 @MODEL

INSERT @Errors
SELECT ERROR_MESSAGE()
WHERE ERROR_NUMBER() <> 0
END

IF @MODEL = 6
BEGIN
EXEC TEST8 @MODEL

INSERT @Errors
SELECT ERROR_MESSAGE()
WHERE ERROR_NUMBER() <> 0
END

IF @MODEL = 7
BEGIN
EXEC TEST9 @MODEL

INSERT @Errors
SELECT ERROR_MESSAGE()
WHERE ERROR_NUMBER() <> 0
END

SET @i = @i + 1
END

SELECT *
FROM @Errors[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources