iif statements in stored procedures

[chainedtodesk]

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],"")

 

[PWise]

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

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

in a Sp use

=isnull(t1outcome,'')+isnull(t2outcome,'')+isnull(t3outcome,'')+isnull(t4outcome,'')+isnull(t5outcome,'')+isnull(t6outcome,'')

anyway what are you trying to do

 

[chainedtodesk]

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.

 

[lameid]

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.