Is there an IIF Function in SQL Server?

treadmill · July 3rd, 2003, 01:21 PM

Hi All,

This is slightly off topic, but I am developing an ASP application using SQL Server.

Does anyone know if there is an equivalent IF function for SQL Server like that of MS Access' IIf function?

Many thanks,

Treadmill

David Cameron · July 3rd, 2003, 06:34 PM

There are two that may fit your requirements. The first is the control statement IF, eg:

Code:

IF @x = 1
BEGIN
    SELECT *
    FROM SomeTable
END

ELSE
BEGIN
    SELECT *
    FROM SomeOtherTable
END

my guess is that you want the other one, CASE, though. CASE is an expression that returns a value, similar to the IFF function in Access. eg:

Code:

SELECT CASE
           WHEN ColVal = 1 THEN 'val1'
           WHEN ColVal = 2 THEN 'val2'
           WHEN ColVal = 3 THEN 'val3'
           ELSE 'something else'
       END AS Translated ColVal
FROM SomeTable

Also there is a SQL Server topic area.

regards
David Cameron

David Cameron · July 3rd, 2003, 06:43 PM

I've just noticed that you posted the problem to the SQL Language forum as well as the ASP forum. Please do not cross post. There are quite a few people who read more than one forum and to post to more than one area means reading the message twice. It also means that the question may be answered twice, as happened in this case, wasting everybody's time. If you have posted the question and it is not answered after a reasonable period of time, then add a comment to your question asking why hasn't anybody helped. If there are no helpful answers, then post the question to another forum.

regards
David Cameron

srikanthvja · December 29th, 2004, 10:35 AM

hi,

thanq for question and ans.
yes, this is fine. we can use it.
but i have query like following
IIF(A.Calc_When = 'Ordered',B.Ordered_Flag =True and B.Ordered_Process = False,
IIF(A.Calc_When = 'Invoiced',B.Invoiced_Flag =True and B.Invoiced_Process = False,
IIF(A.Calc_When = 'Shipped',B.Shipped_Flag =True and B.Shipped_Process = False,
IIF(A.Calc_When = 'Paid',B.Paid_Flag =True and B.Paid_Process = False))) )

this is some nested iff.
here we r using field names in a join query.
so, i this solution not looking it can help me.

so, wand to have a user difined in-line function in sqlserver.

can any body pls help me in this regard?

i have done small work on it i have prepared one function like follow.

** (my intenstion is just replace iff with sqlIIF() SQLSERVER USERDEFINED FUNCTION)

it will not usefull but i want a function like this.
CREATE FUNCTION sqlIIF
(@ExpInString nvarchar(20),@flagYes varchar(10), @flagNo varchar(10))
RETURNS varchar(20)
AS
BEGIN
declare @Return varchar(20)
select @return = case @ExpInString
when 'True' then 'True'
when 'False' then 'False'
end

return @return
end

--- for try to execute
declare @flagval varchar(30)
print dbo.sqliif('True','true','false')

print @flagval

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

thanQ
srikanth

sp_helptext sqliif

Steph · January 17th, 2005, 06:57 AM

I am looking for the same thing but I think the only way is to replace IIF by CASE instruction.