Get System Error Message In Sybase

Question

I am writing a procedure and doing error handling after each step. If an error comes, I return an error code with custom user friendly error message. But due to some requirement, I also have to pass system error message.

Sample :

Ex: I am inserting some records into table and if something goes wrong , I have error message to handle it.

Insert into A
Select top 250 id from C
inner join D
on c.id = D. id


IF (@@error != 0)
BEGIN
    SELECT @p_err_code = 1
    SELECT @p_err_desc = "Error while inserting records into #PAR_PROVIDERS."
    <---------- how to pass system error message here-------->

    DROP TABLE #PAR_PROVIDERS
    RETURN 1
END

Answer 1

I don't know how to take specify error message - I think it's not possible. Maybe below query will cover your needs. It return and pattern message for example Must declare variable '%.*s'. insetad of Must declare variable 'fake variable'.

SELECT description 
from master..sysmessages where error = @@error

@@error variable change every time you make an operation so you need to use local variable for example @err. In your code should be like this.

declare @err int,
        @msg varchar(255)

Insert into A
Select top 250 id from C
inner join D
on c.id = D. id

select @err = @@error

IF (@err != 0)
BEGIN
    SELECT @p_err_code = 1
    SELECT @p_err_desc = "Error while inserting records into #PAR_PROVIDERS."

    SELECT @msg = description 
    from master..sysmessages where error = @err

    DROP TABLE #PAR_PROVIDERS
    RETURN 1
END