SQL Server - obtain error parameters in catch

Question

Possible Duplicate:
How do I find out the _exact_ error from SQL Server

One of our clients desires custom error messages upon UNIQUE KEY violations.

Given the following scenario:

CREATE TABLE [dbo].[Pip]
(
    id int IDENTITY(1,1) NOT NULL,
    code NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_Pip] PRIMARY KEY ([id]), 
    CONSTRAINT [UQ_Pip_Code] UNIQUE([code]), 
)

BEGIN TRY
    INSERT INTO Pip(code) VALUES('TEST')
    INSERT INTO Pip(code) VALUES('TEST')
END TRY
BEGIN CATCH
    DECLARE @error INT, @message VARCHAR(4000), @xstate INT;
    SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();

    IF @errorNumber = 2627 OR @errorNumber = 2601 BEGIN
        RAISERROR('<custom unique key violation error message>', 16, 1)
    END
END CATCH

What I would need is the name of the unique key being violated in the CATCH-section, so that I can specify an error message per defined unique key.

Obviously, I could manually add checks in the different stored procedures accessing this table - however, this leads to less maintainable code, and is not something we would want.

Is there such a possibility in SQL Server (2008R2)?

I've thought about parsing ERROR_MESSAGE() with some sort of regex, based on the sys.messages-table. However, this feels like the wrong thing to do.

Thanks!
Sander

possible duplicate of [How do I find out the \_exact\_ error from SQL Server](http://stackoverflow.com/questions/5411521/how-do-i-find-out-the-exact-error-from-sql-server) and [Sql Server: getting the names of the objects involved in errors](http://stackoverflow.com/questions/8205675/sql-server-getting-the-names-of-the-objects-involved-in-errors?lq=1) — Martin Smith, Jan 14 '13 at 12:24
Martin, thanks. It's indeed a duplicate of the earlier post you indicated. Sorry about that, tried to search but didn't find it. Guess I'll have to look into parsing those messages, then :) — sanderd, Jan 14 '13 at 12:29

score 0 · Answer 1 · answered Jan 14 '13 at 12:25

0

RaiseError has optional parameters, so you could pass in parameters such as the table name and it will substitute.

Straight from the help

RAISERROR (N'This is message %s %d.', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
-- The message text returned is: This is message number 5.
GO

answered Jan 14 '13 at 12:25

Tony Hopkinson

20,172
3
31
39

I was aware of that, but what we are intending to do is 'extracting' the arguments that were defined by SQL-server for the unique key violation error message (2627). ;) – sanderd Jan 14 '13 at 12:27
They are asking how to do the opposite. Extract object names from an error thrown by SQL Server. – Martin Smith Jan 14 '13 at 12:27
'There was key violation in table %s on Key %s you mean','PIP','PK_PIP' would be apposite, not opposite. Only two keys in the table it,s not hard, just high maintenance. If you don't want to do it (I wouldn't), give the customer a suitable quote, might dampen their foolish desires. – Tony Hopkinson Jan 14 '13 at 14:14

SQL Server - obtain error parameters in catch

1 Answers1