# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Using IIf() in a SELECT statement

## Laura Grahame

Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
&#39;IIf&#39; is not a recognized function name
&#39;IsEmpty&#39; is not a recognized function name

Can someone please show me how to get the conditional field value?
Many thanks!!!

SELECT @par_strSquen AS strSquen,
s.strAcctID, 
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS  cenStep) AS strScenStep 
FROM Schedule AS s ...

----------


## rau

I don&#39;t IIF is SQL Server function, i know it is VB function.
Rau


------------
Laura Grahame at 7/6/01 3:15:58 PM

Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
&#39;IIf&#39; is not a recognized function name
&#39;IsEmpty&#39; is not a recognized function name

Can someone please show me how to get the conditional field value?
Many thanks!!!

SELECT @par_strSquen AS strSquen,
s.strAcctID, 
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS  cenStep) AS strScenStep 
FROM Schedule AS s ...

----------


## Anu

I beleive,

IIF and IsEmpty are functions (OLAP services support functions) of Visual Basic for Applications

Check Books Online and search for isempty
you can see the heading Visual Basic for applications
and whichever functions are mention &#39;*&#39; are not supported.

IIF and IsEmpty are the one withn &#39;*&#39; which is not supported.

- Anu





------------
Laura Grahame at 7/6/01 3:15:58 PM

Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
&#39;IIf&#39; is not a recognized function name
&#39;IsEmpty&#39; is not a recognized function name

Can someone please show me how to get the conditional field value?
Many thanks!!!

SELECT @par_strSquen AS strSquen,
s.strAcctID, 
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS  cenStep) AS strScenStep 
FROM Schedule AS s ...

----------


## AHMED

TRY TO USE THE WHEN INSTAED IIF. Its the alternative to IIF in SQL Server. IIF WORKs FINE WHEN YOU EMBED IT IN ACCESS SQL CODE. 

HERE IS THE EXAMPLE:

Use a SELECT statement with a simple CASE function
Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.

USE pubs

GO

SELECT    Category = 

        CASE type

            WHEN &#39;popular_comp&#39; THEN &#39;Popular Computing&#39;

            WHEN &#39;mod_cook&#39; THEN &#39;Modern Cooking&#39;

            WHEN &#39;business&#39; THEN &#39;Business&#39;

            WHEN &#39;psychology&#39; THEN &#39;Psychology&#39;

            WHEN &#39;trad_cook&#39; THEN &#39;Traditional Cooking&#39;

            ELSE &#39;Not yet categorized&#39;

        END,

    CAST(title AS varchar(25)) AS &#39;Shortened Title&#39;,

    price AS Price

FROM titles

WHERE price IS NOT NULL

ORDER BY type, price

COMPUTE AVG(price) BY type

GO



Here is the result set:

Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Business            You Can Combat Computer S 2.99                       

Business            Cooking with Computers: S 11.95                      

Business            The Busy Executive&#39;s Data 19.99                      

Business            Straight Talk About Compu 19.99                      



                                              avg

                                              ==========================

                                              13.73                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Modern Cooking      The Gourmet Microwave     2.99                       

Modern Cooking      Silicon Valley Gastronomi 19.99                      



                                              avg

                                              ==========================

                                              11.49                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Popular Computing   Secrets of Silicon Valley 20.00                      

Popular Computing   But Is It User Friendly?  22.95                      



                                              avg

                                              ==========================

                                              21.48                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Psychology          Life Without Fear         7.00                       

Psychology          Emotional Security: A New 7.99                       

Psychology          Is Anger the Enemy?       10.95                      

Psychology          Prolonged Data Deprivatio 19.99                      

Psychology          Computer Phobic AND Non-P 21.59                      



                                              avg

                                              ==========================

                                              13.50                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Traditional Cooking Fifty Years in Buckingham 11.95                      

Traditional Cooking Sushi, Anyone?            14.99                      

Traditional Cooking Onions, Leeks, and Garlic 20.95                      



                                              avg

                                              ==========================

                                              15.96                      



(21 row(s) affected)



HOPE IT WILL WORK.

AHMED

------------
rau at 7/6/01 3:24:59 PM

I don&#39;t IIF is SQL Server function, i know it is VB function.
Rau


------------
Laura Grahame at 7/6/01 3:15:58 PM

Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
&#39;IIf&#39; is not a recognized function name
&#39;IsEmpty&#39; is not a recognized function name

Can someone please show me how to get the conditional field value?
Many thanks!!!

SELECT @par_strSquen AS strSquen,
s.strAcctID, 
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS  cenStep) AS strScenStep 
FROM Schedule AS s ...

----------


## Laura Grahame

Ahmed,
It worked like a charm!  Thanks so much for your help! :-))

Laura

------------
AHMED at 7/6/01 4:33:48 PM


TRY TO USE THE WHEN INSTAED IIF. Its the alternative to IIF in SQL Server. IIF WORKs FINE WHEN YOU EMBED IT IN ACCESS SQL CODE. 

HERE IS THE EXAMPLE:

Use a SELECT statement with a simple CASE function
Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.

USE pubs

GO

SELECT    Category = 

        CASE type

            WHEN &#39;popular_comp&#39; THEN &#39;Popular Computing&#39;

            WHEN &#39;mod_cook&#39; THEN &#39;Modern Cooking&#39;

            WHEN &#39;business&#39; THEN &#39;Business&#39;

            WHEN &#39;psychology&#39; THEN &#39;Psychology&#39;

            WHEN &#39;trad_cook&#39; THEN &#39;Traditional Cooking&#39;

            ELSE &#39;Not yet categorized&#39;

        END,

    CAST(title AS varchar(25)) AS &#39;Shortened Title&#39;,

    price AS Price

FROM titles

WHERE price IS NOT NULL

ORDER BY type, price

COMPUTE AVG(price) BY type

GO



Here is the result set:

Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Business            You Can Combat Computer S 2.99                       

Business            Cooking with Computers: S 11.95                      

Business            The Busy Executive&#39;s Data 19.99                      

Business            Straight Talk About Compu 19.99                      



                                              avg

                                              ==========================

                                              13.73                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Modern Cooking      The Gourmet Microwave     2.99                       

Modern Cooking      Silicon Valley Gastronomi 19.99                      



                                              avg

                                              ==========================

                                              11.49                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Popular Computing   Secrets of Silicon Valley 20.00                      

Popular Computing   But Is It User Friendly?  22.95                      



                                              avg

                                              ==========================

                                              21.48                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Psychology          Life Without Fear         7.00                       

Psychology          Emotional Security: A New 7.99                       

Psychology          Is Anger the Enemy?       10.95                      

Psychology          Prolonged Data Deprivatio 19.99                      

Psychology          Computer Phobic AND Non-P 21.59                      



                                              avg

                                              ==========================

                                              13.50                      



Category            Shortened Title           Price                      

------------------- ------------------------- -------------------------- 

Traditional Cooking Fifty Years in Buckingham 11.95                      

Traditional Cooking Sushi, Anyone?            14.99                      

Traditional Cooking Onions, Leeks, and Garlic 20.95                      



                                              avg

                                              ==========================

                                              15.96                      



(21 row(s) affected)



HOPE IT WILL WORK.

AHMED

------------
rau at 7/6/01 3:24:59 PM

I don&#39;t IIF is SQL Server function, i know it is VB function.
Rau


------------
Laura Grahame at 7/6/01 3:15:58 PM

Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
&#39;IIf&#39; is not a recognized function name
&#39;IsEmpty&#39; is not a recognized function name

Can someone please show me how to get the conditional field value?
Many thanks!!!

SELECT @par_strSquen AS strSquen,
s.strAcctID, 
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS  cenStep) AS strScenStep 
FROM Schedule AS s ...

----------

