Vardecimal

  • For what? Got an example of the PL/SQL that you're talking about?

    Oracle PL/SQL reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/controlstructures.htm#sthref912

    I will state that the simple syntax example could be done with a SELECT statement, but that is going off-topic.

    The problem with SQL Server's CASE statement is that 1.) each test can only return an expression and 2.) it can only be used within the context of a SQL DML statement. It cannot be used for flow control in T-SQL.

    I occasionally have some some multi-state values being passed into stored procedures. Depending upon the value, different logic will be performed that cannot be done within the context of a SQL DML statement.

    Two simple examples to validate the syntax. Note: SQL Server does not have a "NOP" statement so I used a PRINT command to satisfy the statement requirement between BEGIN ... END blocks.

    Oracle

    -- Oracle PL/SQL CASE logic

    DECLARE

    v_MultiStateVar INTEGER;

    BEGIN

    CASE v_MultiStateVar

    WHEN 1 THEN

    BEGIN

    NULL;

    -- Do stuff

    END;

    WHEN 2 THEN

    BEGIN

    NULL;

    END;

    WHEN 3 THEN

    BEGIN

    NULL;

    -- Do stuff

    END;

    ELSE

    BEGIN

    NULL;

    -- Do stuff

    END;

    END CASE;

    END;

    SQL Server

    -- SQL Server 2005 T-SQL CASE logic

    -- Note: Fails with error:

    -- Msg 156, Level 15, State 1, Line 3

    -- Incorrect syntax near the keyword 'CASE'.

    DECLARE @v_MultiStateVar INTEGER;

    BEGIN;

    CASE @v_MultiStateVar

    WHEN 1 THEN

    BEGIN;

    PRINT '1';

    -- Do stuff

    END;

    WHEN 2 THEN

    BEGIN;

    PRINT '2';

    END;

    WHEN 3 THEN

    BEGIN;

    PRINT '3';

    -- Do stuff

    END;

    ELSE

    BEGIN;

    PRINT 'Else condition';

    -- Do stuff

    END;

    END;

    END;


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • Ah... I see... No big difference there... Use IF on SQL Server... just like you can in Oracle in this "case" 😀

    It sounds like you're worried a bit about code "portability". Although such a concern is noble and will save some time if you ever do have to perform a migration, you don't actually think it'll be 100% portable in all cases, do you?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply