Converting user defined functions to system defined functions

Question

Post reply

Converting user defined functions to system defined functions

prafullaranjan.swain

SSC Enthusiast

Points: 199
More actions
April 25, 2012 at 6:01 am

#273110

Hi Guys,

we are migrating Oracle to sql server 2008 .But in the application which is pointing to sql server is hard coded with to_date and to_char function.For the time being the application people do not want to change the application code which is written in java due to time constraint .So the decision of the management is to write two user defined functions to_date and to_char and add into the data base. I have written two udfs (user defined functions ) but you know while calling the udfs we have to always call with qualifier name like schemaname.udf_name.If they have to do this again they have to change the application code.Now I am inquiring is there any possibility to add convert these udfs to system defined functions so that we can use these with out the qualifiers name.

Any suggestions will be highly appreciated .

Thanks in advance

Prafull

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

I just did a quick proof of concept, and my first thought is you can't do it without some changes/find replace in the code just before you execute it...the functions, whether you mark them as a system object or not, require the owner prefix.

I know i mark procedures as system functions all the time, so they can be called in other databases, even though they reside in master.

functions are more limited,and for cross database calls i thought they also need to start with sp_ (or fn_?)



CREATE FUNCTION TO_CHAR(@input sql_variant)

returns varchar(max)

AS

BEGIN

RETURN CONVERT(varchar(max),@input)

END

GO

EXECUTE sp_ms_marksystemobject 'TO_CHAR'

GO

--Fails: expecting dbo.TO_CHAR

SELECT TO_CHAR(GETDATE()) as TheDate

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!