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?
emadhuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IncisiveOneCommented:
Simple.



BTW, you will have to learn SQL syntax, which is also quite easy.  You will need to maintain an awareness of datatypes, and never mis-match them

-- required parms: caller gets err msg if values not passed or 
-- datatype is mismatched; you need to check if parm is valid value
CREATE PROC my_proc
    @parm_1   int,
    @parm_2   char(30)
AS
    ...
 
-- optional parms: caller gets err msg if datatype is mismatched; 
-- 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) = NULL  -- or "Fubar" or "Sally"
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
        BEGIN
        -- assign value ?
        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), maybe null (which the code can test for)
    --     unless you assigned some other value
 
    ...
 
EXIT_OK:
    RETURN 0
 
EXIT_ERR:
    RETURN -1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jan FranekCommented:
"Optional" parameter have to have default value. And as far as I know, you can't tell apart situation, when parameter was not passed and when parameter was set to the default value.

For example - there's no way to tell, where proc1 was called without parameter (exec proc1) or was called with NULL (exec proc1 NULL).
create procedure proc1 (@param1 int = NULL)
as
if @param1 is null
  print "@param1 NOT PASSED"
else
  print "@param1 PASSED"

Open in new window

0
IncisiveOneCommented:
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

0
Jan FranekCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.