Centrally located stored procs?

Question

Centrally located stored procs?

bagofbirds-767347

Ten Centuries

Points: 1110
More actions
April 20, 2010 at 12:05 pm

#217995

For instance, say i have ten databases all of which contain a copy of the same procedure. is it possible to locate this procedure centrally (in the master db, or a db created for just this purpose) but have it execute against the db (against the tables and data) from which it is called?

Viewing 15 posts - 1 through 15 (of 20 total)

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

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

It is definitely possible. Best practice may dictate creating a table specifically for it, but I can't say for certain. Personally, I've always placed universal functions/procedures in the master database. This has included a series of date manipulation UDFs, a couple of string manipulation UDFs, a couple of database administration SPs, and a single assembly (strconcat).

Remember when you call these SPs/UDFs, you need to use the three-part name: master.dbo.<stored procedure name>, for example.

bagofbirds-767347 Ten Centuries Points: 1110 More actions · Answer 2

thanks. i do indeed use central functions for things like string/date operations. But my problem is getting the procs to work with data in the correct db. if i have the following proc in the master db:

create proc sp_test

as

begin

select * from orders

end

... it is going to look for an orders table in the master db, rather in the db from which i am calling the proc. know what i mean? is it possible to have a central proc that works with the database from which it is called? Thanks for any responses.

Lowell SSC Guru Points: 323518 More actions · Answer 3

bagofbirds-767347 (4/20/2010)
thanks. i do indeed use central functions for things like string/date operations. But my problem is getting the procs to work with data in the correct db. if i have the following proc in the master db:
create proc sp_test
as
begin
select * from orders
end
... it is going to look for an orders table in the master db, rather in the db from which i am calling the proc. know what i mean? is it possible to have a central proc that works with the database from which it is called? Thanks for any responses.

i believe if you mark the proc as a system proc, it will do the automagical [currentdbcontext].[dbo].[tablename], instead of looking for the ].[tablename] in master;

EXECUTE sp_ms_marksystemobject 'sp_test'

i know if you have an "sp_" dll in master,a nd it references sysobjects, it will use the current database context, but if it referneces sys.objects, you must mark it as a system proc for it to use the current database context.

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!

jvanderberg SSCertifiable Points: 6762 More actions · Answer 4

That's a really strange requirement, since generally you don't have the same tables in every database. It's possible, however, but it would have to be done through dynammic sql. You might need to feed the database name to the stored procedure, which would generate a sql string, and execute it.

If that really is your requirement, this might be the best way to do it. There's few things worse than having multiple copies of the same code in different locations.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

As a practice I try to NEVER put anything in master that MS didn't deliver. If I have a case where I have server global objects I generally create a General or ServerUtilities database and store them there.. However, I don't use this for objects that generally query the database, I generally use them to handle a more global function.

I was thinking about the architecture that we are talking about. Why do you have a single sproc that needs to access the local objects? Is this like a multi-tenant application? And if so multiple databases is not usually the best way to handle that.

I guess I'd like to know more about what you are working with..

CEWII

bagofbirds-767347 Ten Centuries Points: 1110 More actions · Answer 6

Lowell - that worked man. awesome!! Thanks so much!

The rest of you - to answer the main question: i do have a handfull of databases that are exactly the same in schema and procs. the data differs for each company we work with. why not put it all in one db, you ask? Great idea, but one that my boss is firmly against. he has his reasons, but they aren't great ones.

Thanks again dudes.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

I've seen this same setup, and it makes sense to me. However I wouldn't centrally locate procs. I have had to move databases to new instances, for performance or security reasons, and it breaks stuff like this. You'd be better off putting the procs in a central db that you create and replicating the procs to each db.

Or just have scripts to deploy them to all dbs. That's not really that hard and it would be better, IMHO, then sticking these in master.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

bagofbirds-767347 (4/21/2010)
Lowell - that worked man. awesome!! Thanks so much!
The rest of you - to answer the main question: i do have a handfull of databases that are exactly the same in schema and procs. the data differs for each company we work with. why not put it all in one db, you ask? Great idea, but one that my boss is firmly against. he has his reasons, but they aren't great ones.
Thanks again dudes.

So the PHB strikes again..

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 9

Elliott W (4/21/2010)
So the PHB strikes again..
CEWII

it took me 3 searches for the definition till i found Pointy Haired Boss, which i should have realized right away...

i have a bunch of procs in master, but they are all for things like metadata searches, documenting the db, and stuff; nothing that assumes a specific table in a certain database; just the base tables like sys.objects and stuff. sp_kill, sp_find,sp_dbDocumentation;

create procedure sp_find

@findcolumn varchar(50)

as

begin

set nocount on

select sysobjects.name as TableFound,

syscolumns.name as ColumnFound

from sysobjects

inner join syscolumns on sysobjects.id=syscolumns.id

where syscolumns.name like '%' + @findcolumn +'%'

or sysobjects.name like '%' + @findcolumn +'%'

order by sysobjects.name

end

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!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Lowell (4/20/2010)
i believe if you mark the proc as a system proc, it will do the automagical [currentdbcontext].[dbo].[tablename], instead of looking for the ].[tablename] in master;
EXECUTE sp_ms_marksystemobject 'sp_test'

Heh... I'll be damned... I've needed to do the same thing and haven't found a way until now. I'll give it a try. Thanks, Lowell.

--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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Elliott W (4/21/2010)
bagofbirds-767347 (4/21/2010)
Lowell - that worked man. awesome!! Thanks so much!
The rest of you - to answer the main question: i do have a handfull of databases that are exactly the same in schema and procs. the data differs for each company we work with. why not put it all in one db, you ask? Great idea, but one that my boss is firmly against. he has his reasons, but they aren't great ones.
Thanks again dudes.
So the PHB strikes again..
CEWII

I know there's lot's of ways to do it but "security" is an excellent reason to maintain different client's data in separate databases. It also allows for customization without have to worry about changing procs names or getting the wrong filters in place for the wrong clients.

--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)

bagofbirds-767347 Ten Centuries Points: 1110 More actions · Answer 12

yeah, i'm kind of ribbing my boss a little; his reasons for maintaining separate dbs are solid, just not from a db design point of view. i think PCI compliance requires this when doing anything with creditcard nums.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Lowell (4/20/2010)
bagofbirds-767347 (4/20/2010)
thanks. i do indeed use central functions for things like string/date operations. But my problem is getting the procs to work with data in the correct db. if i have the following proc in the master db:
create proc sp_test
as
begin
select * from orders
end
... it is going to look for an orders table in the master db, rather in the db from which i am calling the proc. know what i mean? is it possible to have a central proc that works with the database from which it is called? Thanks for any responses.
i believe if you mark the proc as a system proc, it will do the automagical [currentdbcontext].[dbo].[tablename], instead of looking for the ].[tablename] in master;
EXECUTE sp_ms_marksystemobject 'sp_test'
i know if you have an "sp_" dll in master,a nd it references sysobjects, it will use the current database context, but if it referneces sys.objects, you must mark it as a system proc for it to use the current database context.

Gaaahhh... oh well. I made "JBMTest" table in a DB called "SandBox". I made a proc to read from "dbo.JBMTest" in a DB called UTIL. I did the mark system object thing on that proc. I went back to the Sandbox DB, executed the proc using 3 part naming and it didn't find the local table.

Same thing, of course, works if the proc is in Master.

--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)

jvanderberg SSCertifiable Points: 6762 More actions · Answer 14

bagofbirds-767347 (4/22/2010)
yeah, i'm kind of ribbing my boss a little; his reasons for maintaining separate dbs are solid, just not from a db design point of view. i think PCI compliance requires this when doing anything with creditcard nums.

Are partitions an option? You could have all the data in one database, but physically stored seperately...