creating stored procedure in master database

Question

creating stored procedure in master database

Sridhar-137443

Hall of Fame

Points: 3762
More actions
December 19, 2005 at 12:14 pm

#169929

Hi,
I have to create a stored procedure that returns the employee details for our clients. We have a separate database for each client. And each database will have EmpDetails table. Now I need to create a stored procedure to retrieve the employee details and display it on the web. I was thinking of creating a stored procedure with name starting with "sp_" in the master database and call that from the client database. In that way I dont need to create stored procedure in each client database. Or is it better in performance or in some other issues that I need to create the stored procedure in each database? Which is the better option? Please let me know.
Thanks,
Sridhar!!

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

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

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

Ideally you do not want to create stored procedures in the master database. I would recommend creating a "admin" database and creating the proc there. Also, you never want to name your proc beginning with a sp_ for a variety of reasons.

A.J.
DBA with an attitude

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 2

Stored Procedures will run fractionally slower if they start with a prefix of sp_ This is because SQL Server will look for a system stored proc first.

A.J.
DBA with an attitude

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

I'm thinking that's part of the reason he wanted to put it in the Master database although, I agree, putting it in the master is not a good idea. It is better to put it in an ADMIN database or a COMMON database and use the 4 part naming convention to call it from the individual databases.

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

vadba SSChampion Points: 11133 More actions · Answer 4

The only objects I place into my COMMON database are generic SP's and UDF's that don't reference external objects - they operate solely on parameter values. Things like string parsing functions, etc.

Unfortunately, putting a stored procedure that accesses specific tables in a COMMON database won't work as expected. In the shared SP, you have two choices: reference just the table name, or qualify it with the database and owner. For example SELECT * FROM EmpDetails versus SELECT * FROM userdatabase.dbo.EmpDetails. Of course, if the second method is used, then the SP cannot be shared. If the first method is used, the SP will run in the context of the COMMON database, and will not reference the client table. The only way I know of to get it to work is to use dynamic SQL in the SP and pass the database name to the SP as a parameter. Of course, the usual caveats regarding dynamic SQL apply.

CREATE PROCEDURE uspSelectEmpDetails

(

@dbname varchar(50)

)

as

DECLARE @sql varchar(8000)

SET @sql = 'SELECT id, data FROM ' + @dbname + '.dbo.EmpDetails'

EXEC(@sql)

go