Can any body tell me that How Can I use IIf() function in SQLServer2000?

Question

Hello All,
I want to know the use of IIf() in SQLServer2000 SQL Query. My situation is that I want to insert date in SQLServer table if date is not empty else null value will be insert.

I want to use it stored procedure. I tried hard to silve it but fail.

thanking you and waiting for your suggessation. Happy New year 2007

Answer 1

the answer to your prayers is isnull()
isnull takes the @variable and evaluates it for null, if it is null, it returns the second argument:

select
isnull(
@ismyvariablenull,
@valueifitisnull)

if @ismyvariablenull has a value of null, it returns the @valueifitisnull, else it keeps the same value it had before. this is useful for example if you wanted to return currencies for a query, and you have nulls (for whatever reason, left/right joins or something else), you do a select isnull(sum(currencyfield),0) in your grouping query, you get a 0 instead of an awkward null.

I'm not sure where you're going with this, cause the @date parameter if it is null, it should insert null, if it's not null it inserts the value. there is no EMPTY in SQLServer, just null. if the sp is defined:

create procecure sp_isnull(
@date datetime=null
)
begin
insert into table values(@date)
end
go

that should do what you wanted. I really would like for you to elaborate on what you wanted to solve.

Answer 2

CASE is the equivalent of IIF function. See SQL Server Books Online for more information. Here's a quick example:

CREATE TABLE People
(
[ID] int PRIMARY KEY,
[Name] varchar(25) NOT NULL,
Sex bit NULL
)

INSERT INTO People ([ID],[Name], Sex) VALUES (1,'John Dykes', 1)
INSERT INTO People ([ID],[Name], Sex) VALUES (2,'Deborah Crook', 0)
INSERT INTO People ([ID],[Name], Sex) VALUES (3,'P S Subramanyam', NULL)

SELECT [ID], [Name],
CASE Sex
WHEN 1
THEN 'Male'
WHEN 0
THEN 'Female'
ELSE 'Not specified'
END AS Sex
FROM People

Answer 3

check out SQL World @ http://www.pscode.com for examples