How to create a function with boolean parameter?

[oulan]

I want to create a function like IIF in Microsoft Access. But SQL Server 2000 do not have boolean data type. Someone told me bit could be alternative.

The question is, if I define function like:
create function IIF(@boolexp bit, @tv sql_variant, @fv sql_variant) ...

I could

SELECT IIF(COLNAME*3, 'T', 'F') FROM TABLENAME

but could not use

SELECT IFF(COLNAME > 3, 'T', 'F') FROM TABLENAME

So, how to solve that?

Thanks a lot.

[ck9663]

Maybe the CASE expression will do.

Happy Coding!!!

~~ CK

[oulan]

I think you misunderstand me.

I need a function it could accept a boolean expression. I know CASE could do the work. It is not the real function of IIF.

What I need is

SELECT user_function(b oolean_expressi on, ...) ...

[ck9663]

There are no IIF() function in SQL Server. It also does not have Boolean data type, instead it uses a Bit Data Type. Based on the sample you first posting, that could be done in SQL Server but it will take a lot of programming and the technique that you're going to use can degrade the performance of your code.


Good Luck!!!

~~ CK

[oulan]

There are no IIF() function in SQL Server. It also does not have Boolean data type, instead it uses a Bit Data Type. Based on the sample you first posting, that could be done in SQL Server but it will take a lot of programming and the technique that you're going to use can degrade the performance of your code.


Good Luck!!!

~~ CK

Thank you CK.
"Based on the sample you first posting, that could be done in SQL Server but it will take a lot of programming and the technique that you're going to use can degrade the performance of your code."
That maybe mean I still could use a boolean expression in my user defined function?
For example, I want to call a user defined function like
SELECT UDF(COLNAME > 3, 'T', 'F') FROM TABLENAME
COLNAME > 3 is a boolean expression, but it would not be recognized in SQL Server:(
I could not found answer on how to use boolean parameter in MSDN and other site.
I were writing a software to build system with "unified SQL", That's the reason of why I need this function. "case when" need more time on SQL parsing.

Thank you for your answer. I still need know the posibility, or I'll give up.

[ck9663]

Yes you can.
1. Create a function that accepts 3 parameters and returns 1 value.
2. Build a dynamic t-sql that will test your boolean expression.
3. Executed the dynamic t-sql using EXEC and return 'T' for True or 'F' for False.
4. Store that returned value into a variable.
5. Return the value of the variable.

Again, this will drag your process.

Good Luck!!!

~~ CK

[oulan]

CK, Thank you.

CREATE TABLE TABLENAME(
...
COLNAME ...
...
)
go

SELECT UDF('COLNAME > 1', 'T', 'F') FROM TABLENAME

How could exec could return the value of 'COLNAME > 1'?

Maybe only way is write sql parser translate SELECT IIF(...) to CASE WHEN.

Hard work.

Thank you.