GLOBAL FUNCTION

Question

GLOBAL FUNCTION

rs

SSCrazy

Points: 2819
More actions
October 9, 2003 at 4:16 pm

#358372

I want to create a user defined function available to all databases.If you have any idea's please let me know.

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

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Must be in master, prefaced with "fn_" and added to system schema, e.g.:



USE master

go

CREATE FUNCTION fn_Pad(@i bigint, @p tinyint)

RETURNS varchar(20)

AS

BEGIN

  RETURN RIGHT(REPLICATE('0',@p) + CAST(@i AS varchar(20)),@p)

END

go

sp_changeobjectowner 'fn_Pad','system_function_schema'

go

Removal is also tricky:



USE master

go

sp_configure 'allow updates',1

go

RECONFIGURE WITH OVERRIDE

go

DROP FUNCTION system_function_schema.fn_Pad

go

sp_configure 'allow updates',0

go

RECONFIGURE WITH OVERRIDE

go

--Jonathan

philcart SSC-Forever Points: 47794 More actions · Answer 2

It doesn't matter where you create the function. You can specify the database name when you call the function.

eg: MyDb.dbo.MyFunction

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

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

rs SSCrazy Points: 2819 More actions · Answer 3

Jonathan,

Thanks, it worked for me. But is it compatable for all versions?.

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

quote:
Jonathan,
Thanks, it worked for me. But is it compatable for all versions?.

All versions that support UDFs. That would be SQL Server 2000.

--Jonathan

rs SSCrazy Points: 2819 More actions · Answer 5

Jonathan,

If i want edit that what is the procedure.