Can SQL Server do a Conditional Statement in a SubQuery? 1

[rvancleef]

I am creating a wizard and have a tabs table to dynamically build navigation. I need to return a recordset with an Active/Inactive Value for each record (tab) to render the tab in the appropriate format.

I have 2 queries, one to return the records, and a second to determine Active/Inactive that work seperately, but how do I join the Active/InActive evaluation to the larger query returning the recordset? If I make a seperate SP call for each record that seems inefficient.

Here is my best shot
(plagued by Syntax Errors:
Incorrect syntax near the keyword 'IF'.
Line 19: Incorrect syntax near 'Active'):

SELECT *,
(
IF (SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CheckTab)<=(SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CurrTab)
BEGIN
'Active'
END
ELSE
'InActive'
)AS tStatus
FROM Tabs
WHERE ParentTabID = 1001
ORDER BY Tabs.TabOrder

Please let me know any thoughts.....

 

[rac2]

Use the CASE expression instead of If

SELECT 
       CASE
            WHEN conditions1 THEN expression1
            WHEN conditions2 THEN expression2
            ELSE expression3
       END AS "Complicated Idea",
       ...
FROM ...

 

[SQLBill]

Look up CASE in the Books OnLine (BOL).

Another issue is this:

BEGIN
'Active'
END

BEGIN is used for the start of a transaction. But nothing is happening between the BEGIN and END. You are using BEGIN with the IF as though the BEGIN is a THEN.

-SQLBill

 

[rvancleef]

We are getting closer. I am now getting a result set without Syntax errors when I give the @CurrTab and @CheckTab fixed values. I implemented the Case Statement as follows:

SELECT *,
Case
When (SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CheckTab)<=(SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CurrTab) THEN 'Active'
When (SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CheckTab)>(SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CurrTab) THEN 'InActive'
End
AS tStatus
FROM Tabs
WHERE ParentTabID = 1001
ORDER BY Tabs.TabOrder

But now how do we replace the @CurrTab variable with TabID of each record as the recordset is built?

 

[swampBoogie]

Like this

SELECT *,
Case
    When (SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CheckTab)<=(SELECT Tabs.TabOrder FROM Tabs WHERE TabID=qt.tabid) THEN    'Active'
    When (SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CheckTab)>(SELECT Tabs.TabOrder FROM Tabs WHERE TabID=qt.tabid) THEN    'InActive'
End
AS tStatus  
FROM Tabs as qt
WHERE ParentTabID = 1001
ORDER BY qt.TabOrder

or did I misunderstand something?

By the way, it is a case expression, not a case statement (which is something else in SQL).

 

[SQLBill]

Or maybe:

DECLARE @CurrTab INT
SET @CurrTab = (SELECT Tabs.tabid FROM Tabs)

<rest of query>

Of course, if tabid is not INT then change the first line to whatever datatype it should be.

-SQLBill

 

[rvancleef]

Perfect!!!!!

Now how does it work?
1. What is happening with the 'qt'? How is that a part of the solution?
2. What is the difference between a case expression and case statement? I must have done something wrong for you to call my attention to this point.

Thank you VERY much for your help on this!!!

 

[swampBoogie]

The qt is an arbitray alias so the tabs table can be referenced in the subquery.

if you used

When (SELECT Tabs.TabOrder FROM Tabs WHERE TabID=@CheckTab)>(SELECT Tabs.TabOrder FROM Tabs WHERE TabID=tabs.tabid) THEN    'InActive'

it would be interpreted as a reference to the instance of tabs used in the subquery.

SQL (not T-sql in this version) has support for a case statement with the syntax

case 
  when-clause then
     statements
  when-clause then
     statements
   ...
  else
     statements
end case

So, I just like to call things by their proper names.