Possible to have "public" functions?

[Wrathchild]

My team develops in-house applications in Access w/Sql Server back-ends. I was wondering if it's possible for us to share SQL functions? Right now we basically copy functions into our database to make them available; what I would like is to have a common area where we can place functions to have them available. Seems like it would have to be higher than database level, but don't think that's possible. thanks!

 

[markros]

One possibility is to created these functions in master database and use master.dbo.myFunction(...)

or have a separate database to store common functions and use
CommonDB.dbo.myFunction(...)

Otherwise you may want to copy these functions into every database.

PluralSight Learning Library

 

[gmmastros]

I would recommend a separate database to store common functions.

You can also make use of synonyms (if you are using sql2005 or higher) so that you don't need to modify your code. Let me explain...

Suppose you have a function already that does something, and you copied that function to several different databases, and now you want to clean it up. When you have the function in a common database, you would need to modify your code to include the database name, like markros showed you...

CommonDB.dbo.myFunction(...)

But, what you can do is to create a synonym for the function in each user database. Like this...

use [YourUserDatabase]
go
CREATE SYNONYM [dbo].[myFunction] FOR [DBA].[dbo].[myFunction]

After creating the synonym, you no longer need to add the database name to the function call. Any code you already created to use the function locally, would now work to use the function from the common database.

Basically....

1. create the function in the common database.
2. drop the function from the user database.
3. create the synonym in the user database.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom