iif stored procedure

[Anabella]

Hi all,

I have a bunch of queries initially written for MS Access that contain iif
function.
Since I'm new in SQL Server and I don't like the idea of rewriting all
queries using CASE statement, I hope someone has written iif T-SQL stored
procedure.
If someone have T-SQL code of such procedure I would appreciate you post
this code here.

Thanks in advance!

[Simon Hayes]

"Anabella" <[e-mail]> wrote in message news:cu**********@ls219.htnet.hr...

Hi all,

I have a bunch of queries initially written for MS Access that contain iif
function.
Since I'm new in SQL Server and I don't like the idea of rewriting all
queries using CASE statement, I hope someone has written iif T-SQL stored
procedure.
If someone have T-SQL code of such procedure I would appreciate you post
this code here.

Thanks in advance!

Since IIF doesn't exist in TSQL, I don't think you have any other option.
The Upsizing Wizard might help, but the most reliable thing to do would be
to rewrite them.

Simon

[Andy O'Neill]

"Anabella" <[e-mail]> wrote in message news:cu**********@ls219.htnet.hr...

Hi all,

I have a bunch of queries initially written for MS Access that contain iif
function.
Since I'm new in SQL Server and I don't like the idea of rewriting all
queries using CASE statement, I hope someone has written iif T-SQL stored
procedure.
If someone have T-SQL code of such procedure I would appreciate you post
this code here.

Thanks in advance!

I think maybe you could write a udf which took parameters.
I think handling the various comparisons might be hard work.
Off the top of my head, I think you'd have to change the syntax of your
query a bit so you handed the comparison as a parameter.
And... erm... changing your query is what you wanted to avoid.

I've got some other bad news for you as well.

MS Access sql is slightly different from sql server sql .
I would suggest you need to go through all your queries and check em out.
Changing iif to case statements is likely just part of the task you face.

--
Regards,
Andy O'Neill

[Erland Sommarskog]

Andy O'Neill (ao***************@lycos.co.uk) writes:

I think maybe you could write a udf which took parameters.
I think handling the various comparisons might be hard work.
Off the top of my head, I think you'd have to change the syntax of your
query a bit so you handed the comparison as a parameter.
And... erm... changing your query is what you wanted to avoid.

Since boolean is not a datatype in SQL Server, you cannot write a
UDF which permits you to say:

SECECT dho.iif(@x = @y, 1, 2)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Andy O'Neill]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Andy O'Neill (ao***************@lycos.co.uk) writes:

I think maybe you could write a udf which took parameters.
I think handling the various comparisons might be hard work.
Off the top of my head, I think you'd have to change the syntax of your
query a bit so you handed the comparison as a parameter.
And... erm... changing your query is what you wanted to avoid.

Since boolean is not a datatype in SQL Server, you cannot write a
UDF which permits you to say:

SECECT dho.iif(@x = @y, 1, 2)

I had in mind handing over the comparison operation as a string.
So check if the comparison to be made is varchar and '=' or maybe '>' or
'<'.
If that makes sense.

That's the part I'm actually most confident I could handle.
I didn't even begin to think about the various datatypes one could have for
other parameters....

--
Regards,
Andy O'Neill