Solved

replacement to iif function in sql 2005

Posted on 2014-03-23
25
1,221 Views
Last Modified: 2014-03-27
Hi all,
What is the best way to create a function in sql that would perform as the IIF function in VBA?
0
Comment
Question by:bfuchs
  • 8
  • 8
  • 4
  • +3
25 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39949219
Hi,

I don't think that there is such a beast, at least not a one-to-one replacement.

Implied in your question is that SQL is a procedural language like VB. It is not. It is a functional language.

To best help, can you post an example of what you are trying to achieve. If iif is being used as control flow, then you'll need to use if then else. If iif is being used within a query then you can use the case statement

select
    case
        when getdate() < '2014-03-24' then 'yesterday'
        when getdate() < '2014-03-25' then 'today'
        else 'tomorrow'
      end
;

HTH
  David
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39949238
(Waiting in line at the Rick Springfield concert)

Click on my name, scroll to the bottom of my profile to Articles, and find the one titled SQL Server CASE Solutions.  Enjoy.
0
 
LVL 14

Expert Comment

by:Juan Ocasio
ID: 39949243
The IIF statement just evaluates an expression and then returns something based on the truth of the stratement.  So if you have


Iff(MyNumber >1000, "Yes", "No")

In SQL, you could create a block similar to this:


IF ( Boolean_expression )
   BEGIN
        sql statement for true
   END
ELSE
   BEGIN
        sql staement for false
   END
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39949461
Hi experts,

Thanks for replying,

I know that there are ways to accomplish that in different ways, like those you're mentioning, however since I am using it very often in my select statements, it would be convenient to have this work as in vba (or as I saw written that sql 2012 has it).

I believe this can be accomplished in t-sql as well, with a function that accepts 3 parameters.

@Jim Horn,

Didn't see there something titled as you're mentioning, can you please provide a link to that post?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39949495
Hi,

I believe that Jim is referring to this:
http://www.experts-exchange.com/Database/MS-SQL-Server/A_12366-SQL-Server-CASE-Solutions.html

There are a few more clicks required to find it.

HTH
  David
0
 
LVL 14

Expert Comment

by:Juan Ocasio
ID: 39949935
Why don't you create a function called I iif and put the logic there. Then you can use it as needed.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39950699
You said your BE is SQL Server 2005.  If you are using T-SQL directly via pass-through queries, you are limited to T-SQL syntax.  If you are creating querydefs in Access, you can use Access SQL syntax or VBA or UDF functions.  HOWEVER, care must be taken when using VBA or UDF functions.  Neither can be passed to the server so if you use the function in any clause other than Select, you run a high risk of forcing Access to request the entire table or tables from the server so that it can process the query locally.

Remember SQL Server does not support VBA.  It cannot process any of your functions.  The ODBC driver converts Access SQL and whatever VBA functions have equivalents such as the IIF() to valid T-SQL before sending the query to the server for processing.  It is best to not get in the way and what you are looking for has a high potential for making your queries inefficient.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39951444
Hi experts,

Why don't you create a function called I iif and put the logic there. Then you can use it as needed.

Unfortunately I am not so familiar with the t-sql language and this is why I thought your guys would be a good  in helping me accomplish that.

@David Todd,
I only saw there example of how to use it in 2012, but as described, I need it in 2005.

@PatHartman,
I want to create a view in the BE and display it in ADP FE, therefore it wouldn't be possible to use anything that belongs to the vba functionality..

FYI- In the following link I saw someone suggesting sort of this function, however I would really prefer take it from the experts here...(Or at least getting an approval that this is going to work).

http://www.dbforums.com/microsoft-sql-server/755706-possible-create-iif-function-sql-server.html
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39951944
If you are working in T-SQL, then you have to use the T-SQL function which is a Case statement.  You can't use IIf() since that is VBA.

I'm really not sure where you are going with this or why you think you need it.  Replicating an IIf() would be pretty complex since you would have to get into recursion.  If you don't do that, the functionality is severely limited so what's the point?

Why isn't the Case statement working for you?  That is what the ODBC driver converts IIf()'s to.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39952280
hi, the reason I want a function is simply because every time I get into this, I simply forget how to do it and have to start searching for the syntax..I guess msft also realized that isn't so elegant and therefore built into the 2012 version..
As for the logic, If I would be doing in VBA, it would work as follows
public function MyIIF(param1 as variant, param2 as variant, param3 as variant, param4 as variant) as variant
 if param1=param2 then MyIIF = param3 else MYIIf = param4 end if
end function
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39953792
Usually I run into people who are trying to mold Access into the image of whatever development platform they prefer.  This is the first time I've ever seen someone wanting to change some other platform to be more "Access like".  While I don't like discouraging innovation and I really like VBA, I don't think this is a good idea.  Since you will not be replacing the Case statement's functionality, your myIIf() function will have limited usefulness and a limited lifespan.  This is also the kind of thing that your successors will curse you for because they will inherit it and have to figure out how to use it even after the database has been upgraded.  You are using a 9 year old product for which there are at least three newer versions.  You only want to do this because the environment is less user-friendly than that of a newer version.  Try upgrading to the express version of A2012 instead.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39953829
You would really need a function for each type of comparison -- eq, ne, gt, etc. -- because the function can't dynamically evaluate the operator, only the operands.

Here's a sample function for IIF_eq, and a sample invocation of it:


CREATE FUNCTION dbo.IIF_eq (
    @param1 sql_variant,
    @param2 sql_variant,
    @param3 sql_variant,
    @param4 sql_variant
)
RETURNS sql_variant
AS
BEGIN
RETURN (
    SELECT CASE WHEN @param1 = @param2 THEN @param3 ELSE @param4 END
)
END --FUNCTION
GO

SELECT dbo.IIF_eq(CAST('20140325' AS datetime), CAST('03/25/2014' AS datetime), 'Equal', 'Not Equal')
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:bfuchs
ID: 39954630
@PatHartman
Try upgrading to the express version of A2012 instead.
Do you think that will run smoothly for my ADP front end application?

@ScottPletcher
You would really need a function for each type of comparison
How about adding another parameter to determine that?
also how do I add an option to check if first parameter is null instead of comparing to a value?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39954665
I doubt it.  Why was I supposed to know you had an ADP as a FE?

How about adding another parameter to determine that?
Unless T-SQL has the equivalent of the VBA Eval() function, this isn't going to work.  And even with Eval() it might not.  Changing the relational operator changes the structure of the statement.  The statement you are trying to create isn't a string.  You could probably do this if your code wrote the function based on the arguments.  Think about how you would do this in VBA.

IIf(argument1, argument2, argument3)

You can't just substitute strings for each argument.  Substitution is for data values not language elements.  But you might be able to get
IIf(Eval(argument1), Eval(argument2), Eval(argument3)) to work.

Expending the effort to rewrite as a standard Access app with linked tables would be more productive.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 500 total points
ID: 39954666
CREATE FUNCTION dbo.IIF (
    @param1 sql_variant,
    @param2 sql_variant,
    @comparison char(2) = '=',    
    @param3 sql_variant,
    @param4 sql_variant
)
RETURNS sql_variant
AS
BEGIN
RETURN (
    SELECT CASE
        WHEN @param2 IS NULL THEN CASE WHEN @param1 IS NULL THEN @param3 ELSE @param4 END
        WHEN @comparison = '=' THEN CASE WHEN @param1 = @param2 THEN @param3 ELSE @param4 END
        WHEN @comparison IN ('<>', '!=') THEN CASE WHEN @param1 <> @param2 THEN @param3 ELSE @param4 END
        WHEN @comparison = '<' THEN CASE WHEN @param1 < @param2 THEN @param3 ELSE @param4 END
        WHEN @comparison = '>' THEN CASE WHEN @param1 > @param2 THEN @param3 ELSE @param4 END
        --WHEN @comparison IN ('%', 'LK') THEN CASE WHEN @param1 LIKE @param2 THEN @param3 ELSE @param4 END
        ELSE @param4 END --nothing found to compare, return "false"      
)
END --FUNCTION
GO


SELECT dbo.IIF(1, 2, DEFAULT, 'Eq', 'NotEq')
SELECT dbo.IIF(1, 2, '>', 'Gt', 'NotGt')
SELECT dbo.IIF(1, 2, '<', 'Lt', 'NotLt')
SELECT dbo.IIF(NULL, NULL, '*', 'Null', 'NotNull')
SELECT dbo.IIF('A', NULL, '*', 'Null', 'NotNull')
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39954672
Working on a better version, will post a.s.a.p..
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39954725
This is the best I can do ... although it was a lot of fun, can't spend any more time on it (at least right now :-) ):


CREATE FUNCTION dbo.IIF (
    @condition varchar(200),
    @true_result sql_variant,
    @false_result sql_variant
)
RETURNS sql_variant
AS
BEGIN
RETURN (
    SELECT
        CASE
        WHEN operator_start = 0 THEN @false_result        
        --NULL will require other methods using this approach to the function
        --WHEN value2 IN ('IS NULL', 'NULL') THEN CASE WHEN value1 IS NULL THEN @true_result ELSE @false_result END
        WHEN operator = '=' THEN CASE WHEN value1 = value2 THEN @true_result ELSE @false_result END
        WHEN operator IN ('<>', '!=') THEN CASE WHEN value1 <> value2 THEN @true_result ELSE @false_result END
        WHEN operator = '<' THEN CASE WHEN value1 < value2 THEN @true_result ELSE @false_result END
        WHEN operator = '>' THEN CASE WHEN value1 > value2 THEN @true_result ELSE @false_result END
        WHEN operator IN ('!>', '<=', '=<') THEN CASE WHEN value1 <= value2 THEN @true_result ELSE @false_result END
        WHEN operator IN ('!<', '>=', '=>') THEN CASE WHEN value1 >= value2 THEN @true_result ELSE @false_result END
        ELSE @false_result END --nothing found to compare, return "false"
    FROM (
        SELECT PATINDEX('%[=<>!]%', @condition) AS operator_start
    ) AS assign_values_1
    CROSS APPLY (
        SELECT 1 + CASE WHEN SUBSTRING(@condition, operator_start + 1, 1) LIKE '[=<>]' THEN 1 ELSE 0 END AS operator_len
    ) AS assign_values_2
    CROSS APPLY (
        SELECT SUBSTRING(@condition, operator_start, operator_len) AS operator
    ) AS assign_values_3
    CROSS APPLY (
        SELECT LTRIM(RTRIM(LEFT(@condition, ABS(operator_start - 1)))) AS value1,
            LTRIM(RTRIM(SUBSTRING(@condition, operator_start + operator_len, 200))) AS value2
    ) AS assign_values_4
)
END --FUNCTION
GO

SELECT
    condition,
    dbo.IIF(condition, 'Yes', 'No') AS [Result?]
FROM (
    SELECT 100 AS MyNumber, '>' AS operand UNION ALL
    SELECT 1005, '>' UNION ALL
    SELECT 10009, '<>' UNION ALL
    SELECT 1000, '<>' UNION ALL
    SELECT 1, '!<'
) AS test_data
CROSS APPLY (
    SELECT CAST(MyNumber AS varchar(10)) + operand + '1000' AS condition
) AS ca1
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39954726
Hi Scott, Just want to verify if your function would be able to handle all types of parameters (like dates, strings, integer etc) or there is a need to add another parameter to indicate that?

@PatHartman
Sorry, you didn't had to know that I'm using ADP, I just meant to ask  what is your experience in that type of conversion/upgrade, as it may be useful for us in the future.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39954735
Good point ... the first version will, but perhaps not the second, even if we cast it to sql_variant, since it's coming from a string:

...
CROSS APPLY (
        SELECT CAST(LTRIM(RTRIM(LEFT(@condition, ABS(operator_start - 1)))) AS sql_variant) AS value1,
            CAST(LTRIM(RTRIM(SUBSTRING(@condition, operator_start + operator_len, 200))) AS sql_variant) AS value2
    ) AS assign_values_4
...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39954763
If you want a full evaluation, with substitution, you would have to use:

EXEC sp_executesql

Since that's a proc, it can't be called within a SELECT list.  That is, you can't do this:

SELECT
    EXEC sp_executesql '...','...'
FROM dbo.tablename
WHERE ...
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39954984
Hi Scott,

I see you are really putting big efforts here, appreciate that & hope it will work out, just have 2 questions

1- what is the difference between the two versions?

2- from what I understand, the first version will handle all types and the second only strings, if that's correct, I would create both and use them where appropriate.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39956336
The second allows a full expression, like IIF normally does, like "value1>value2".
But, since that means it's reading an expression from a string, the result automatically becomes a string -- we'd have to add a parameter for data type to allow different data types.

The first requires that you split all the parts of the comparison as input:
value1, value2, >

Honestly, I'd use sp_executesql for this type of thing, even if I had to scroll thru a cursor to do the evaluations.  As long as you're talking 100K rows or less, it won't be too slow, and it will add the full power of SQL functions to your evaluation capabilities.
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 39957207
Thanks allot Scott, great job.

If you don't mind, when you have a chance:

1- please elaborate more on how to use this sp_executesql

2- At the moment I need this to evaluate if something is null, when tried your second suggestion (even when uncommented that null line) I could not got it to work, therefore I am currently using the first one, however if its not a big job, let me know how can I make that to work for the second, so I have a full functional code in one place.

And thanks to all experts involved in this thread!!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39957665
2- I'd use '=NULL' in the comparison to test for NULL, then make this change to the function:

...
RETURN (
    SELECT
        CASE
        WHEN operator_start = 0 THEN @false_result
        WHEN operator = '=' AND value2 = 'NULL' THEN CASE WHEN value1 IS NULL THEN @true_result ELSE @false_result END
        WHEN ...
...
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39960249
Hi Scott,

Attaching both, the function including this latest change and also the sql/results.
for some reason it always returns the false part, regardless if the condition is true or false,  let me know what I am doing wrong?

Thanks
Ben
Doc2.doc
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now