Solved

If else condition in Sybase 12.5

Posted on 2009-07-06
4
9,231 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:emadhu
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
IncisiveOne earned 300 total points
ID: 24788397
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
 
LVL 14

Assisted Solution

by:Jan_Franek
Jan_Franek earned 200 total points
ID: 24792221
"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
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24793044
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
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 24793126
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sybase license update 3 471
SQL Syntax 6 390
compare Sybase and MSSQL server 18 748
sybase space calculatio, why must divide / multiply by 512 ? 3 126
Note: This is the third blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   We’ve been talki…
A customer recently asked me about anti-malware and the different deployment options available for his business. Daily news about cyberattacks, zero-day vulnerabilities, and companies that suffered a security breach made him wonder if the endpoint a…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

16 Experts available now in Live!

Get 1:1 Help Now