public Function

Question

public Function

thava

SSCrazy

Points: 2275
More actions
February 15, 2012 at 1:25 am

#255753

is it possible to create a function which is available for all Databases(i.e)
when i create a procedure with the prefix of SP_ in the master Database it is visible to all other Database but is it possible to create such Function in mssql server, or is any other way to do it
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

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

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Yes - nothing special is required - on an instance of SQL Server, you can access functions in a database from any other database (subject to access rights), using suitably qualified object names (database.schema.fn_fname()).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Phil Parkin (2/15/2012)
Yes - nothing special is required - on an instance of SQL Server, you can access functions in a database from any other database (subject to access rights), using suitably qualified object names (database.schema.fn_fname()).

Phil is 100% right. Just for design consideration, I would advise creating separate database (for example you can call it GlobalUtils) and put all of you "public" procs and functions into it and grant to public to execute them all.

BTW, it's not very good idea to add procs with sp_ prefix into master database.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

I agree with Eugene. Probably not a good idea to put all of these types of things in master. I also agree that a separate database is good for these types of things but I don't agree that you should use fully qualified names in your code. Stick to the 2 part naming convention and use synonyms to point to the functions. That way, if you ever buy 3rd party software that just happens to use your "function repository" database, you don't have a whole bunch of code to change... just a handful of synonyms.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)