Formatting RAISERROR messages in SYBASE ASE 12.5

Question

In a stored procedure, I am trying to test if a parameter is null or less then 1, and if so, raise an error with a friendly message, and the value of the parameter.

Here is my code:

IF @ID IS NULL OR @ID <= 0
BEGIN
    RAISERROR 27001 'ID is ?????. ID cannot be null or less then zero.'
    RETURN 27001
END

What I would like back is either:

"ID is 0. ID cannot be null or less then zero." or "ID is null. ID cannot be null or less then zero."

I've tried building a varchar and raising the error like this:

RAISERROR 27001 @message

but that produces the error "Message Number 27001, passed to RAISERROR, does not exist in the sysmessages catalog."

Answer 1

The problem is this: You need to add your custom error # into the database by calling sp_addmessage:

sp_addmessage 27001, "ID is %1. ID cannot be null or less than zero."

You can then call RAISERROR like you have above, or like this (if you want to use the message string you defined in sp_addmessage):

RAISERROR 27001, @ID

EDIT: (Addressing comment)

First, if you're so inclined just register 27001 with a positional argument as the message:

sp_addmessage 27001, "%1"

Second, you're not FORCED to display the default message that you've registered using sp_addmessage; e.g., this ought to display a separate message regardless of what the default message is:

RAISERROR 27001 @message

Answer 2

This works, although an @ID value of NULL writes out as empty string.

IF @ID IS NULL OR @ID <= 0
BEGIN

    DECLARE @msg varchar(250)
    SELECT @msg = 'ID is ' +  Convert(varchar(15), @ID) + '. Value cannot be null or less then zero'

    RAISERROR 27001 @msg
    RETURN 27001
END