• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3389
  • Last Modified:

SYBASE SQL

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



0
irodov
Asked:
irodov
1 Solution
 
irodovAuthor 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.
0
 
grant300Commented:
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....

BEGIN
  DECLARE @EXSTAT    INT
  EXEC @EXSTAT = my_proc_here @Arg1, ......
  IF @EXSTAT != 0
    BEGIN
      ROLLBACK
      RAISERROR 20108, 'SPNAME', 'OTHER MESSAGE ARGUMENTS AS REQUIRED'.....
      RETURN(-1)
    END
  RETURN
END

Regards,
Bill
0
 
josteinleiraCommented:
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.
 
 
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now