asked on

If else condition in Sybase 12.5

I would like to code a Stored Procedure depending the on IN params that are passed to the SP

example: EXEC Abc_SP 'Param1'

Note: Param1 is optional

Inside the SP, i would like to check if the Param1 is available

if (Param1 is passed) {

if (Param1 == 'A') {
-- do this
} else {
-- do something else
}
} else {
-- do this
}

How do I code this in Transact SQL Stored Procedure?

ASKER CERTIFIED SOLUTION

IncisiveOne

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jan Franek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IncisiveOne

Jan

1 That might be true in theory, but not in practice. We have to be sensible here. Take a real situation, a real parameter, not a theoretical parameter. Eg. sp_who. The parameter is spid. What is the difference between:

exec sp_who

and

exec sp_who null

The answer is none, there is no difference.

2 By deifinition, null is not a value, or more accurately, it means "no value". Therefore it would be completely incorrect for the proc to accept null as a value of any kind. It should only signify that no value was passed. And anyone explicitly passing null means they are passing "no value".

3 Therefore, by definition, null cannot be the default, because the default is a real value.

4 But, if you wanted code that would capture and respond to such a situation (keeping these rules in mind), here it is.

Cheers

-- you need to check if parm is supplied AND if parm is valid value
CREATE PROC my_proc
    @parm_1   int      = 20,
    @parm_2   char(30) = "Jan"  -- default value
AS
    DECLARE  @my_var   int
    ...
 
    IF @parm_1 < 0 OR @parm_1 > 100
        BEGIN
        PRINT "parm_1 must be between 0 and 100."
        GOTO EXIT_ERR
        END
 
    IF @parm_2 = NULL   -- caller was being silly
        BEGIN
        parm_2 = "Jan"
        END
    ELSE
        IF @parm_2 < CHAR(48) OR @parm_2 > "ZZZZZZ"
            BEGIN
            PRINT "parm_2 is invalid."
            GOTO EXIT_ERR
            END
    ...
 
    -- parm_1 is int, 0 to 100
    -- parm_2 in char(30)
 
    ...
 
EXIT_OK:
    RETURN 0
 
EXIT_ERR:
    RETURN -1

Open in new window

Jan Franek

OK, I just reacted to original question "i would like to check if the Param1 is available" and said, that you can't allways know, whether optional parameter was supplied. However, you are right - in most real-life cases you don't need that information.