Vardecimal

• Mauve

  SSChampion

  Points: 11316

  More actions

  March 21, 2008 at 7:41 am

  #792441

  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]

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  March 21, 2008 at 9:46 am

  #792556

  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)