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

Question

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

Hamlet DRC

Old Hand

Points: 325
More actions
March 7, 2005 at 1:00 am

#60546

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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You could try and make the input and output datatypes sql_variant. It's not overly efficient, and may cause problems with implicit conversion, but should work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hamlet DRC Old Hand Points: 325 More actions · Answer 2

Hmmm...

This is my first MS-SQL function so I'm not sure why it is not working.

I create it like this:

CREATE FUNCTION nvl (@one sql_variant, @two sql_variant)

RETURNS sql_variant

AS

BEGIN

DECLARE @return_value sql_variant

SET @return_value=isnull(@one, @two)

RETURN (@return_value)

END

And I call it like this :

select nvl('123', '321') from my_table

By Query Analyzer comes back with a function not found.

I see the function in Enterprise Manager, and I gave myself permissions on it.

Any ideas?

Thanks in advance.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ownership confusion, most likely

First it should be

CREATE FUNCTION dbo.nvl(....

then call it

select dbo.nvl('123', '321') from my_table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hamlet DRC Old Hand Points: 325 More actions · Answer 4

You are correct, it is ownership confusion.

But my goal is to call my nvl() function without

having to supply an owner in the query.

I want to call it by running:

select nvl('123', '321') from my_table

I tried changing permissions on the nvl object but I couldn't get it to work. Do you know how to make this object visible without qualifying the function with an owner name?

philcart SSC-Forever Points: 47795 More actions · Answer 5

You won't be able to setup your function that way.

From Books Online,

Calling User-Defined Functions
When calling a scalar user-defined function, you must supply at least a two-part name:

You're going to have to make a change to add 'dbo.' to the start of the function call, so you might as well replace the whole lot with ISNULL.

--------------------
Colt 45 - the original point and click interface

Hamlet DRC Old Hand Points: 325 More actions · Answer 6

Thank you.

That settles it, the answer is NO!, I cannot do what I'm trying to do.

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 7

A while ago SQL Server Magazine website posted a method to create system functions in master that could be called from any database without prefixing the owner's name. It was an undocumented method because it involved changing the owner in master.sysobjects, but it was effective.

One caveat was that the function had to start with "fn_" and I think be in all lowercase. Also, it's a bit of work to drop the function if you no longer need it.

If you're up for something like this,check out:

http://www.windowsitpro.com/SQLServer/Articles/ArticleID/15544/pg/2/2.html

(I don't think you need to log on to access this one).

Scott Thornburg

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 8

Odds are there are other things you have to tweak between the two environments. If those things are consistent, maybe you should look at automating your find/replace routine. Write a short script (Perl, VBScript, whatever works for you) and run your oracle code through it before using it in SQL server.

Of course, if you're lucky enough that the nvl/isnull diff is the only thing you need to change, the way you've been doing it probably is your best bet.

R David Francis

Hamlet DRC Old Hand Points: 325 More actions · Answer 9

There are many differences and I have grown intimitely familiar with them over the last two years of doing multi-platform development.

If anyone cares...

The biggest culprits in our SQL commands are the nvl/isnull function, the decode/case statement, the (+)/outer join syntax, and the type conversion functions.

Our server code actually provides a translation layer for all of this so we don't have to worry about it and can pick the format we prefer... but you have to be working with my company's tools and I vastly prefer MS-SQL Query Analyzer to our homegrown SQL workbench.

I realized there are a lot of differences between the two databases, but I figured that if I could make a simple nvl() look like an isnull() then I could continue and just write the rest of the functions in a matter of minutes.

Thanks for all the input... sadly it led to a dead end