How to write a MS-SQL function that makes nvl() look like isnull()

[HamletDRC]

I routinely have to take Oracle SQL statements and run them against a SQL server database, and I find myself always doing search and replace to convert nvl() fuctions to isnull() functions.

I want to write a MS-SQL function that will translate and nvl() occurances in a SQL statement into isnull().

So I want to be able to run this:
select nvl('123', '321') from mytable
in MS-SQL w/out copy/pasting isnull().

I tried to create a user defined function for this but didn't get it to work b/c a function requires data types in the parameter declarations.

Any ideas?

 

[donutman]

Write a VB app that takes the contents of the clipboard (your copy of entire query) translates it from Oracle SQL to MSSQL...ready for your paste.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

If you want to basically write a wrapper function for ISNULL then something like this should work:

CREATE FUNCTION nvl(
	@test_val sql_variant,
	@null_val sql_variant
)
RETURNS sql_variant
AS
BEGIN
	RETURN ISNULL(@test_val, @null_val)
END

You can see it works with:

--char data
SELECT dbo.nvl('char val', 'foo'),
	dbo.nvl(CAST(NULL AS varchar), 'val was null')

--int data
SELECT dbo.nvl(123, 999),
	dbo.nvl(CAST(NULL AS int), 666)

However, you still have the problem that you have to specify the owner when calling UDFs. So you would still need some find/replace to add the dbo in front of the nvl calls.

--James

 

[donutman]

Aside from the performance hit of making it a UDF.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Write translation app/class/whatever. Oracle and T-SQL syntax differ in many other details.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[HamletDRC]

The function works, except I still have to prefix my call with dbo.

Since my goal is to not have to do search and replace, do you know if it is even possibly to create a function that doesn't need to be called with the owner specified?

 

[HamletDRC]

I was able to get an answer to this from a different source.

It is not possible to do what I am trying to do.
The MS-SQL function calling interface always requires you to call a function using the owner.function format. So there is no way to make a function that can be called as nvl(), it will always have to be called as dbo.nvl().

Answer taken from MS-SQL books online.

 

[donutman]

Always a good idea to check-up on Jimmy...a man with that many stars can't always be right!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]