Using Conditional Logic in Query 1

[jessedh]

Hello all...

here in my problem. I am moving from Access to SQL server for my ASP pages. I want to migrate the queries from Access to stored procedures. I am haing all sorts of trouble figuring out conditional logic and getting it to display properly. A simple example is...

Access SQL: IIf([Pre_app]='','Active','Inactive') AS Active,

MS SQL that doesn't work...
IF dbo.dbo_MSXDMAIN.pre_app LIKE '' THEN 'ACTIVE' ELSE 'INACTIVE') AS Active,

Can someone just give me the framework for conditional statements (IF and CASE) so I can get this project done???!

Thanks!

 

[JamesLean]

There is no IIF function in T-SQL, you need to use a CASE expression:

SELECT CASE WHEN pre_app = '' THEN 'Active' ELSE 'Inactive' END AS Active

--James

 

[jessedh]

What about if you need to use 'LIKE'... Can I just drop it in there?

 

[JamesLean]

Yes, you can use any expression to test against. Look up CASE in BOL for full info.

--James

 

[jessedh]

How do you add a 'WHERE' condition on a conditionally created field. Do i have to add the statement again, or can is there a trick to minimize the logic testing....

 

[JamesLean]

Two options.

1) Repeat the CASE expression in the WHERE clause:

SELECT foo, CASE WHEN x THEN y ELSE z END AS bar
FROM t
WHERE CASE WHEN x THEN y ELSE z END = 123

2) For more complicated expressions you can do something like:

SELECT foo, bar
FROM (
    SELECT foo, CASE WHEN x THEN y ELSE z END AS bar
    FROM t
  ) dt
WHERE bar = 123

--James

 

[jessedh]

SELECT foo, bar
FROM (
SELECT foo, CASE WHEN x THEN y ELSE z END AS bar
FROM t
) dt <-- What is this for?
WHERE bar = 123