August 4, 2009 at 2:52 am
Hi Guys
i Got an Problem To Implement The Commaon Error Handling Mechnism in Sql 2005.
i Created Comman Error Handler Sp in That iam raising both Custom message as well
as an Actual sql execption but iam not able implement, igot some problem in nested sps,
When iam Try Raise On Error Through Comman Error Handling Sp i got The
Problem
> In Error Message it is not giving Actual sp Name which has An Error
> in Nested Sp if The Child sp has Error it is Not Raising An Both Custom Message
and Sql execption how to solve this Problem.
Instead Of Raising an Error we Can Select an Error Mesage then it wil An Correct Error Report
but I need to Raise The Error ..
the Below is the Method I tried.,
--
CREATE TABLE Sample
(
Id INT Primary Key Identity,
UserName Nvarchar(255) UNIQUE NOT NULL
)
/*
-- Adding an Custom Message
EXEC sp_addmessage @msgnum = 60000, @severity = 16,
@msgtext = N'The Input Not In Correct Format'
-- select * from sys.messages WHERE Message_ID = 60000
*/
-- Comman Sp To Raise Error
CREATE PROCEDURE UspGetError
AS
BEGIN
DECLARE @Msg NVARCHAR(MAX)
SELECT @Msg = 'ErrMsg :' + ERROR_MESSAGE()
+' ErrProcedure :'+ ERROR_PROCEDURE()
+ ' ErrNo : '+CONVERT(VARCHAR,ERROR_NUMBER())
RAISERROR(60000,16,1) -- Custom Message
RAISERROR(@Msg,16,1) -- Actual Message
END
GO
-- Parent Sp
CREATE PROCEDURE UspParent
@Name Nvarchar(MAx),
@child bit = 0
AS
BEGIN
BEGIN TRY
IF (@Child = 0)
BEGIN
INSERT INTO Sample(UserName)
SELECT @Name
END
ELSE
BEGIN
Exec UspChild @Name = @Name
END
END TRY
BEGIN CATCH
Exec UspGetError
END CATCH
END
GO
-- Child Sp'
CREATE PROCEDURE UspChild
@Name Nvarchar(MAx) = null
AS
BEGIN
BEGIN TRY
UPDATE Sample SET UserName = @Name
END TRY
BEGIN CATCH
Exec UspGetError
END CATCH
END
-- Inserting The Initial value
INSERT INTO Sample(UserName) SELECT 'User1'
-- Parent Sp Execution
EXEC UspParent @Name = 'User1',@Child = 0
/*
-- output:
Msg 60000, Level 16, State 1, Procedure UspGetError, Line 8
The Input Not In Correct Format
Msg 50000, Level 16, State 1, Procedure UspGetError, Line 9
ErrMsg :Violation of UNIQUE KEY constraint 'UQ__Sample__C9F284561E6F845E'.
Cannot insert duplicate key in object 'dbo.Sample'. ErrProcedure :Uspparent ErrNo : 2627
-- But The Actual OutPut I Need is
Msg 60000, Level 16, State 1, Procedure Uspparent, Line 8 -- Custom Message
The Input Not In Correct Format
Msg 50000, Level 16, State 1, Procedure Uspparent, Line 9 -- Sql Error
ErrMsg :Violation of UNIQUE KEY constraint 'UQ__Sample__C9F284561E6F845E'.
Cannot insert duplicate key in object 'dbo.Sample'. ErrProcedure :Uspparent ErrNo : 2627
*/
-- Child Sp Execution
EXEC UspParent @Name = NULL ,@Child = 1
-- output
/*
Msg 60000, Level 16, State 1, Procedure UspGetError, Line 8
The Input Not In Correct Format
Msg 50000, Level 16, State 1, Procedure UspGetError, Line 9
ErrMsg :The Input Not In Correct Format ErrProcedure :UspGetError ErrNo : 60000
*/
-- But The Actual OutPut I Need is
/*
Msg 60000, Level 16, State 1, Procedure UspChild, Line 8 -- Custom Message
The Input Not In Correct Format
Msg 50000, Level 16, State 1, Procedure UspChild, Line 9 -- Sql Error
ErrMsg :Cannot insert the value NULL into column 'UserName', table 'Sample.dbo.Sample'; column does not allow nulls.
UPDATE fails. ErrProcedure :UspChild ErrNo : 515
*/
Please Help me Guys and Please Give me Suggestions For This..
Thanks in Advance 🙂
Deepak.A
August 4, 2009 at 8:17 am
You could add a parameter to uspGetError to pass the procedure name (OBJECT_NAME(@@procid)).
-- Gianluca Sartori
August 4, 2009 at 10:10 pm
hi Gianluca Sartori
Thanks For your Reply can you please give some more details about this iam try to implement this methos but stil iam not getting the Actual error message . still it is not giving the Correct Error Procedure name
and i need to Raise Both Custom message and Sql Exception in raise statement
Thanks
Deepak.A
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply