I need to get the error description from the SQL Server in a SP. For ex:
I have one insert statement which is inserting some values in a tabUserMaster table. If user tries to insert any duplicated row then following error is retruned [in Query analyzer].
Server: Msg 2601, Level 14, State 3, Procedure csp_ProvisionUser, Line 70
Cannot insert duplicate key row in object 'CoreUser' with unique index 'IDX_CoreUser_UserName'.
The statement has been terminated.
I want to trap this whole message in a variable. How to do this.....
...since I'm not sure what you'd like to do once you have captured it, I'm not sure how to answer...but if you lookt at BOL and search on error it will bring up several topics which might apply (e.g. @@error)...
if @in_ErrorNumber > 0
insert into <table> values (dbo.fun_error_desc(@err_no,@vr_JobNumber))
create function fun_error_desc(@err_no int)
declare @err_message varchar(200)
select @err_message = description from master.dbo.sysmessages where error=@err_no