-2

I'm using try...catch instructions to handle errors in SQL Server and I need to recognize the object (e.g. code of the field that has been faced with error) so that I can show my own message instead of default system error message.

Here is my stored procedure:

USE [ArbitraryDB]    
GO
SET ANSI_NULLS ON    
GO    
SET QUOTED_IDENTIFIER ON    
GO

ALTER PROCEDURE [dbo].[CTInsert_UserSkills]    
(    
@UserID_int int, @SkillSN_int int, @Ordering_tinyint tinyint,     
@SkillLevelSN_tinyint tinyint, @SkillYearcount_tinyint tinyint    
)    
AS    
BEGIN    
SET NOCOUNT ON;

DECLARE @ErrorMessage varchar(200), @ErrorCode int    
BEGIN TRY

INSERT INTO CT_UserSkills (UserID_int, SkillSN_int, Ordering_tinyint,     
SkillLevelSN_tinyint, SkillYearcount_tinyint)    
VALUES (@UserID_int, @SkillSN_int, @Ordering_tinyint, @SkillLevelSN_tinyint,     
@SkillYearcount_tinyint)    
END TRY

BEGIN CATCH    
SELECT ERROR_MESSAGE()    
END CATCH

END

and here is the output:

Cannot insert duplicate key row in object 'dbo.CT_UserSkills' with unique index 'IX_CT_UserSkills'. The duplicate key value is (4, 1).

but actually I'm looking to find '%.*ls' in pattern of below error message with code 2601 in sysmessages:

Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. 
The duplicate key value is %ls.

Would you please guiding me on finding that ('%.*ls')?

Farshid
  • 5,134
  • 9
  • 59
  • 87

1 Answers1

0

Add another OUTPUT parameter to the stored procedure:

ALTER PROCEDURE [dbo].[CTInsert_UserSkills]    
(    
@UserID_int int, @SkillSN_int int, @Ordering_tinyint tinyint,     
@SkillLevelSN_tinyint tinyint, @SkillYearcount_tinyint tinyint,    
@ObjectID int OUTPUT
)

Then in your catch block, set this variable to whatever value you need to send back to the calling program.

Sam Anwar
  • 669
  • 5
  • 8
  • Dear Sam, It doesn't mean how to send a variable value to the outside world but how to find %.*ls. – Farshid Sep 02 '12 at 10:57
  • It is all about that 'whatever value you need to send back'. It is a specific thing and the question is all about how that value can be found. – Farshid Sep 02 '12 at 10:58
  • You know, voting down people who are trying to help you out just because you didn't understand the answer is extremely rude. – Sam Anwar Nov 24 '12 at 07:07