We help IT Professionals succeed at work.

SYBASE SQL

irodov
irodov asked
on
3,741 Views
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 g.id = @prmId
                having count(sc.prod_group) >0

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

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



Comment
Watch Question

Author

Commented:
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
      END

But it is displaying as below:

1> exec delProductGroup 1, 'ss'
2> go
 rtncode
 ----------- --------------------------------------------------
          -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)
1>

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.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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.
 
 
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.