March 21, 2008 at 7:41 am
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;
March 21, 2008 at 9:46 am
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
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply