none
MS Access IIF in SQL Server

    Question

  • I have a table, BusinessEntity with three fields:
    IsPerson bit
    Name1 nvarchar(64)
    Name2 nvarchar(64)

    If this is a Person, then Name1 contains the First Name, Name2 contains the Last Name.

    If this is not a Person, then Name1 contains the Full Name, Name2 contains the Sort Name.

    In MS Access, I can write the following Select Statement:

    SELECT BusinessEntity.BusinessEntityID, BusinessEntity.IsPerson, IIf([BusinessEntity].[IsPerson]=True,[BusinessEntity].[Name1] & ' ' & [BusinessEntity].[Name2],[BusinessEntity].[Name1]) AS FullName, IIf([BusinessEntity].[IsPerson]=True,[BusinessEntity].[Name2] & ', ' & [BusinessEntity].[Name1],[BusinessEntity].[Name2]) AS SortName
    FROM BusinessEntity;

    I would like to be able to create a SQL Select Statement for use in a View as well as other places and this is a function that I use a lot.

    This doesn't work in SQL Server - the IIF function does not exist. Any guidance on how to best do this would be much appreciated.


    Many thanks,

    Flavelle
    Monday, November 14, 2005 4:53 PM

Answers

  • IIF becomes CASE. SELECT FullName = CASE IsPerson WHEN 1 THEN Name1 + ' ' + Name2 ELSE Name1 END, SortName = CASE IsPerson WHEN 1 THEN Name2 + ', ' + Name1 ELSE Name2 END FROM BusinessEntity; Some other things you might come across: http://www.aspfaq.com/2214 wrote in message news:90b5d9e2-7626-4278-8cdb-1f544ac936aa@discussions.microsoft.com... >I have a table, BusinessEntity with three fields: > IsPerson bit > Name1 nvarchar(64) > Name2 nvarchar(64) > > If this is a Person, then Name1 contains the First Name, Name2 contains > the Last Name. > > If this is not a Person, then Name1 contains the Full Name, Name2 > contains the Sort Name. > > In MS Access, I can write the following Select Statement: > > SELECT BusinessEntity.BusinessEntityID, BusinessEntity.IsPerson, > IIf([BusinessEntity].[IsPerson]=True,[BusinessEntity].[Name1] & ' ' & > [BusinessEntity].[Name2],[BusinessEntity].[Name1]) AS FullName, > IIf([BusinessEntity].[IsPerson]=True,[BusinessEntity].[Name2] & ', ' & > [BusinessEntity].[Name1],[BusinessEntity].[Name2]) AS SortName > FROM BusinessEntity; > > I would like to be able to create a SQL Select Statement for use in a > View as well as other places and this is a function that I use a lot. > > This doesn't work in SQL Server - the IIF function does not exist. Any > guidance on how to best do this would be much appreciated. > > > Many thanks, > > Flavelle >
    Monday, November 14, 2005 5:04 PM

All replies

  • IIF becomes CASE. SELECT FullName = CASE IsPerson WHEN 1 THEN Name1 + ' ' + Name2 ELSE Name1 END, SortName = CASE IsPerson WHEN 1 THEN Name2 + ', ' + Name1 ELSE Name2 END FROM BusinessEntity; Some other things you might come across: http://www.aspfaq.com/2214 wrote in message news:90b5d9e2-7626-4278-8cdb-1f544ac936aa@discussions.microsoft.com... >I have a table, BusinessEntity with three fields: > IsPerson bit > Name1 nvarchar(64) > Name2 nvarchar(64) > > If this is a Person, then Name1 contains the First Name, Name2 contains > the Last Name. > > If this is not a Person, then Name1 contains the Full Name, Name2 > contains the Sort Name. > > In MS Access, I can write the following Select Statement: > > SELECT BusinessEntity.BusinessEntityID, BusinessEntity.IsPerson, > IIf([BusinessEntity].[IsPerson]=True,[BusinessEntity].[Name1] & ' ' & > [BusinessEntity].[Name2],[BusinessEntity].[Name1]) AS FullName, > IIf([BusinessEntity].[IsPerson]=True,[BusinessEntity].[Name2] & ', ' & > [BusinessEntity].[Name1],[BusinessEntity].[Name2]) AS SortName > FROM BusinessEntity; > > I would like to be able to create a SQL Select Statement for use in a > View as well as other places and this is a function that I use a lot. > > This doesn't work in SQL Server - the IIF function does not exist. Any > guidance on how to best do this would be much appreciated. > > > Many thanks, > > Flavelle >
    Monday, November 14, 2005 5:04 PM
  • That works very well - thanks. and the link is an excellent source for some of my other questions.
    Monday, November 14, 2005 5:19 PM