×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# iif statements in stored procedures

## iif statements in stored procedures

(OP)
i cant seem to get the following iif statement to work in a stored procedure, is this possible? i am trying to update 3 possible table fields based on criteria collected from a table merge. thanks

=IIf([t1outcome] Is Not Null,[t1outcome]+", ","")+IIf([t2outcome] Is Not Null,[t2outcome]+", ","")+IIf([t3outcome] Is Not Null,[t3outcome]+", ","")+IIf([t4outcome] Is Not Null,[t4outcome]+", ","")+IIf([t5outcome] Is Not Null,[t5outcome]+", ","")+IIf([t6outcome] Is Not Null,[t6outcome],"")

### RE: iif statements in stored procedures

this post is not an adp question rather a sql question, but i will respond
1)iif takes only 3 prams
2)this can be written

#### CODE

=nz(t1outcome,"")+nz(t2outcome,"")+nz(t3outcome,"")+nz(t4outcome,"")+nz(t5outcome,"")+nz(t6outcome,"")+

in a Sp use

#### CODE

=isnull(t1outcome,'')+isnull(t2outcome,'')+isnull(t3outcome,'')+isnull(t4outcome,'')+isnull(t5outcome,'')+isnull(t6outcome,'')
anyway what are you trying to do

### RE: iif statements in stored procedures

(OP)
sorry for posting in the wrong forum, i used something similar to this in an MDB and the current project i am working with is an ADP, adding in the stored procedure  gave me errors. what this is doing is looking at a medical questionaire and based on responses there could be up to 6 answers, and i am trying to create one string that i can merge to a letter within the program. i will give these a go, thank you for responding.

### RE: iif statements in stored procedures

Only because it is the subject and others might look here: in a select statement you could use CASE WHEN to replace the JET IIF.  Procedurally, T-SQL has an IF Then statement.

Case WHEN X = Y Then B CASE X = Z Then C ELSE A End

You can use as many cases you want this way... Common use is more like the VBA Select Case Statement.  Check out Books Online (BOL) for common syntax.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!