iif stored procedure

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!"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.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|||"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.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|||Andy O'Neill (aon14nocannedmeat@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95F7EFAAF416CYazorman@.127.0.0.1...
> Andy O'Neill (aon14nocannedmeat@.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