Results 1 to 7 of 7
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    User Defined IIf Function


    I have several queries in SQL Server 2012 that use the intrinsic system function IIf. I need to port these to a SQL Server 2008 R2 system that does not have IIf as an intrinsic system function. Does anyone have a user defined dbo.IIf function you're willing to share that I can use in place of the IIf intrinsic function? Thanks, Eddie

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You mean something like:

    Public Function IIf(booCondition As Boolean, IfTrue As Variant, IfFalse As Variant) As Variant
    If booCondition Then
    IIf = IfTrue
    Else
    IIf = IfFalse
    End If
    End Function

    Then call the function same as intrinsic IIf.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    Yes, something like this except... I need to make this a SQL User-Defined Scalar Function, not a VBA one. It needs to reside in the SQL Server Database Programmability, Functions, Scalar-valued Functions section in SQL Server Management Studio. Does this help?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Via Google: http://windowsitpro.com/windows/what...and-sql-server
    Code:
    A. In Microsoft Access you would use:
    
                                  Select iif(field>10,"large", "small") as Size from Table
    
                                  With SQL Server, use the CASE command
    
                                  SELECT Size = 
                                  CASE
                                  WHEN field > 10 THEN "large"
                                  ELSE "small"
                                  END
                                  from Table
    Also:
    http://www.techonthenet.com/sql_serv...tions/case.php

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Doesn't help me. I've never done any development in SQLServer.

    Take the logic of the function I show and do whatever you would do in SQLServer to write code. Could use Select Case in my example instead of If Then Else or:

    Public Function IIf(booCondition As Boolean, IfTrue As Variant, IfFalse As Variant) As Variant
    IIf = Switch(booCondition, IfTrue, Not booCondition, IfFalse)
    End Function

    Unless SQLServer doesn't have Switch() function either.

    http://www.databasejournal.com/featu...RE-Clauses.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    have you tried the case [fldA] when ... then ... else ... end SQL syntax?

    kind regards
    NG

  7. #7
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Quote Originally Posted by NoellaG View Post
    Hi,

    have you tried the case [fldA] when ... then ... else ... end SQL syntax?

    kind regards
    NG
    I second this

    CASE WHEN (BOOLEAN TEST) THEN (TRUE OUTCOME) ELSE (FALSE OUTCOME) END

    and it's even better than IIF style because you can chain them.

    CASE WHEN (BOOLEAN TEST 1) THEN (TRUE OUTCOME 1) WHEN (BOOLEAN TEST 2) THEN (TRUE OUTCOME 2) WHEN (BOOLEAN TEST 3) THEN (TRUE OUTCOME 3) ELSE (ALL FALSE OUTCOMES) END

    Cheers,

    Jeff

Please reply to this thread with any new information or opinions.

Similar Threads

  1. User Defined Function Returns Ambiguous Name Error
    By HotsauceHero in forum Modules
    Replies: 8
    Last Post: 10-03-2014, 04:09 PM
  2. How to use User Defined Function in VBA
    By sbehera in forum Programming
    Replies: 7
    Last Post: 05-12-2014, 07:38 AM
  3. Replies: 9
    Last Post: 10-12-2013, 07:19 AM
  4. Replies: 6
    Last Post: 09-09-2011, 10:26 AM
  5. Query using a User defined function for dates
    By RonanM in forum Programming
    Replies: 4
    Last Post: 06-16-2011, 04:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums