How to translate oracle decode

Hi,

How to translate oracle Decode without changed code I mean:

I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I

Wrote this function

select dbo.fnDecode( 1 , 2 , 3 )

first parameter always int, and the others parameters could be char, int or float.

I would like to test first value and return second or third value

-> sql_variant for all parameters, ok

but I can use + or -

I can't do this

select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )

If I put cast ok, but I is there another way, overload this call?

With clr doesn't work, because Sql Server doesn't accept function overload calls from C#

Any ideia?

cheers,

Any ideia?

Use explicit cast/convert for both operands?

|||There is no easy way to do what you are trying i.e., replacing Oracle's built-in DECODE function with TSQL/SQLCLR equivalent. The sql_variant approach will work to some extent but you will run into issues when you try to use operators on the value as you experienced. It will require explicit CASTING in lot of cases. And using TSQL UDF will introduce performance problems especially in DMLs if used incorrectly. SQLCLR approach doesn't work because we don't support overloaded methods. Best is to convert uses of DECODE to CASE expression. Note that CASE expression is actually ANSI SQL standard and more portable way to write SQL code. In fact, Oracle supports it from 7.3 or 8i so there is no reason to use DECODE in Oracle too. As stop gap measure, you could write different set of TSQL UDFs (decode, decode1, decode2 etc) each handling different data types. And when you hit performance problems with your queries/DML that uses these UDFs you can convert to CASE expression i.e., inline the code.|||I can't use cast/convert, because I never know what kind of value will be, so I dont know what kind of cast if to char or to int,float ....