Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 3rd, 2003, 01:21 PM
Registered User
 
Join Date: Jun 2003
Location: Banbury, Oxon, United Kingdom.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Is there an IIF Function in SQL Server?

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
  #2 (permalink)  
Old July 3rd, 2003, 06:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old July 3rd, 2003, 06:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old December 29th, 2004, 10:35 AM
Registered User
 
Join Date: Dec 2004
Location: chennai, TAMILNADU, India.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



  #5 (permalink)  
Old January 17th, 2005, 06:57 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
iif function rohit_sant SQL Server 2000 11 December 24th, 2009 04:04 AM
help with IIf function rashi Access VBA 3 February 15th, 2008 10:07 AM
IIf Function - Multiple conditions carrie09 Access 2 June 13th, 2007 10:27 AM
EVAL becomes failed, with IIF and Replace Function AbrarNazeer ASP.NET 2.0 Professional 0 July 6th, 2006 06:53 AM
sql server function mateenmohd SQL Server 2000 1 June 12th, 2003 04:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.