Post reply

Converting user defined functions to system defined functions

  • 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

  • April 25, 2012 at 6:21 am

    #1478900

    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!

  • April 25, 2012 at 9:28 am

    #1479103

    You are short of luck here. You can't call function without schema prefix.

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • April 25, 2012 at 1:38 pm

    #1479317

    I tried creating a synonym for the function but that requires the schema as well so no luck there. I think you're completely out of luck on this one.

  • April 26, 2012 at 3:35 pm

    #1480108

    Allow user defined functions to be called in the same manner as Builtin functions

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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