Posted on 2007-10-10
Last Modified: 2009-08-20
Experts - can you please help:
I have the sp which has some piece of code as below:
      if (
      exists (
            select 1
            FROM GrpTb g
            LEFT OUTER JOIN ccProfile sc
            ON sc.prod_group = g.group_name
            where g.group_type = 'PRDGRP'
            and = @prmId
                having count(sc.prod_group) >0

            select @varErrorDescription = 'The group ' || convert(varchar,@prmId) || ' can not be deleted, this group is in use.'
            raiserror 99999, @varErrorDescription

so above checks -- if group is in use --- it should raise an error with the description that group is in use can so cannot be deleted.

But I am getting messages as below:
in results :

1> exec delProductGroup 1, 'ss'
2> go
Msg 2748, Level 16, State 1:
Server 'RDS', Procedure 'delProductGroup', Line 30:
Message number 99999, passed to RAISERROR, does not exist in the sysusermessages catalog.
Procedure 'delProductGroup' attempted to return a status of NULL, which is not allowed.  A status of 0 will be returned instead.
(return status = -6)

What is happening? please help

Question by:irodov

    Author Comment

    I don't know if it is good idea to use raiserror or not since for each sp I will need to do this:
    so I tried other way:

    select @varErrorDescription = 'The group ' || convert(varchar,@prmId) || ' can not be deleted, this group is in use.'
                select -1 rtncode, @varErrorDescription

    But it is displaying as below:

    1> exec delProductGroup 1, 'ss'
    2> go
     ----------- --------------------------------------------------
              -1 The group 1 can not be deleted, this group is in u

    (1 row affected)
    Procedure 'delProductGroup' attempted to return a status of NULL, which is not allowed.  A status of 0 will be returned instead.
    (return status = 0)

    it truncated the description, but I need to display this on UI.
    Please help..

    which way is better?
    please help me fix the way which is good.
    LVL 19

    Accepted Solution

    Two things:

    First, you need a RETURN -1 after each RAISERROR.

    Second, 99999 is not a valid user message number.  First you have to create a user defined error message and second, it has to be in a certain range; 20000 to 21000 if I remember correctly.  No matter, it is documented.

    One last thing.  Checking @@ERROR after calling a stored procedure is pretty worthless.  What you want to do is capture the return code.  At the end of successful completion of an SP, the last statement should be 'RETURN'  This returns a zero exit status code which means normal completion.  Any non-zero (generally negative) return code signifies an error.  You do that by using the RETURN(-1) or some other number at the exit points where errors are trapped.  You can return different codes to mean different things to your application if you like.

    When you call a stored proc you capture the exit status the following way....

      EXEC @EXSTAT = my_proc_here @Arg1, ......
      IF @EXSTAT != 0

    LVL 1

    Expert Comment

    Using ASE 15.0.2 I got the same error message.

    From the booklet "The Complete Sybase ASE Quick Reference Guide" coveringe ASE versions 12.0, 12.5, 12.5.1 and 12.5.2 I got the following information:
    1) There should not be any comma between the error number and a variable

    raiserror 99999, @varErrorDescription -- Wrong
    raiserror 99999 @varErrorDescription --Right
    2) Valid userdefined error numbers must be > 20000 (so 99999 should be OK).
    3) The userdefined error must be registered in the sysusermessages table. Use the sp_addmessage to add your own error messages.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now