April 19, 2010 at 12:52 pm
I have two StoredProcedures SP1,SP2.In both StoredProcedures,I am using Try/Catch structure for catching error messages.in SP2,I am calling SP1.I need to do some special operation,in case of primary key violation(2627) Error Code in SP1.But now I am only recieveing errorCode:50000 in SP2,for any problem in SP1.Is there a way,that I can recognize the error code 2627,that has happened in SP1, in SP2?
Thanks
April 19, 2010 at 2:18 pm
There may be a better way to do it, but I know you can set an output parameter and assign it the value of the error code (or 0 if there is no error). You can do a conditional statement in your other sproc based on the value of that output parameter.
April 19, 2010 at 4:42 pm
TRY/CATCH traps the error... so it is being handled by program logic in the catch section. If you want the nested stored procedure to return an error, you can use the RAISERROR statement:
http://msdn.microsoft.com/en-us/library/ms178592.aspx
Or, you can just have it can populate an output variable as previously suggested and test for that. But such a test should be done in the TRY section.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 19, 2010 at 11:49 pm
The following code shows how to use the procedure return code to identify the error number:
USE tempdb;
GO
CREATE PROCEDURE dbo.SP1
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Test
(
primary_key INTEGER NOT NULL
CONSTRAINT [PK #Test primary_key]
PRIMARY KEY
);
INSERT #Test(primary_key) VALUES (1);
BEGIN TRY
INSERT #Test (primary_key) VALUES (1);
END TRY
BEGIN CATCH
DECLARE @em NVARCHAR(2048),
@ep NVARCHAR(126),
@EN INTEGER,
@EL INTEGER,
@ES INTEGER,
@et INTEGER;
SET @em = ERROR_MESSAGE();
SET @ep = ERROR_PROCEDURE();
SET @EN = ERROR_NUMBER();
SET @EL = ERROR_LINE();
SET @ES = ERROR_SEVERITY();
SET @et = ERROR_STATE();
RAISERROR('Error %i in %s at line %i: %s', @ES, @et, @EN, @ep, @EL, @em);
RETURN @EN;
END CATCH
END;
GO
DECLARE @rc INTEGER;
EXECUTE @rc = dbo.SP1;
PRINT @rc;
GO
DROP PROCEDURE dbo.SP1;
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2010 at 12:18 pm
Thanks a lot for your reply.But,I just was wondering if I can re-produce primary key violation in Second(SP2) by using Raiserror.It seems that RaiseError does not have any explicit Erronumber ,input parameter.and this Errornumber is part of Option input parameter,which is not accessible in SP2.
April 20, 2010 at 12:33 pm
Here is Example,
For Instance I am trying to run the following statment:
2627:Error Code for primary violation
raiserror(2627,16,1)
the result of running this statment:
Msg 2732, Level 16, State 1, Line 1
Error number 2627 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.
But Actually ErrorCode 2627 exists in sysmessage(master table)
Also I used the following link:
http://msdn.microsoft.com/en-us/library/ms177497.aspx
I am somehow confused.
April 20, 2010 at 6:05 pm
Aspet Golestanian Namagerdi (4/20/2010)
Thanks a lot for your reply.But,I just was wondering if I can re-produce primary key violation in Second(SP2) by using Raiserror.
No, you are limited in the range of error numbers you can raise using RAISERROR - see the documentation.
This makes sense, if you think about it. You can't have user code raising errors that mean specific things to the database engine. The code I posted is one good workaround for this, just test the return code rather than ERROR_NUMBER().
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply