InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 14:53:13 GMT20http://www.sqlservercentral.com/Forums/Topic424172-149-1.aspx"You are not likely to run into errors from adding procedures to the master database"... umm, maybe/maybe not. Master is a system database and effectively belongs to MS - yes you can put your own stuff in there at the risk of (1) breaking something or (2) having a service pack or hotfix break something for you...Thu, 22 Nov 2007 00:35:40 GMTJoe Cliffordhttp://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxThanks for your input, it's much appreciated!:)Wed, 21 Nov 2007 06:58:44 GMTr.vanlaakehttp://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxDealing with this as a deployment issue would probably be the best approach. Create a process or an application that will apply changes across all of the databases. Once approach to this may be to create a single database with just your stored procedures in it and a process that replicates all of the procedures to your other databases when a ddl event fires. This could be managed pretty easily through triggers or even SQL replication and would ensure nothing ended up out of sync.Regardless of how you do this, I would try to establish this type of infrastructure right away and make sure everyone sticks to it.Wed, 21 Nov 2007 06:51:13 GMTMichael Earl-395764http://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxThanks again Michael (and others)My preference indeed is to skip the conditionals and the dynamics. On the other hand, the number of stored procedures would run into the 100's. I am building an ASP website, and all calls to the database are stored procedures (to make a conversion to asp.net later more simple).On the ASP side, all customers have their own website. Every .asp file basically consists of two lines:[i]#include customer settings#include common file[/i]So, with any number of customers, there's always just one file for me to update (the common file).Obviously I would like to have the same type of setup for the database. It would be a drag to have to update the same Stored Procedure 50 times for each of the 50 customers. Probably the best would be is, after a Stored Procedure has been created or modified in a test enviroment, run a script that creates/modifies all the counterparts in the customer databases.Thanks,RaymondWed, 21 Nov 2007 05:59:32 GMTr.vanlaakehttp://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxYou are not likely to run into errors from adding procedures to the master database.What you do have to watch out for is making sure you appropriately back up your system databases, be aware that if you transfer a database to another server that you may have broken some logic. You also have to watch out for a service pack or something that could potentially replace or remove your stored procedure.I don't like adding objects to the system databases mostly because they are system databases and I like to leave them alone. For what you are doing, I probably would just put the procedure into the master database and ignore all of the dynamic SQL or conditionals. However, if you find yourself adding more than 10 or so procedures to the system databases, I would say you may have a design problem.Wed, 21 Nov 2007 05:04:01 GMTMichael Earl-395764http://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxYes, you are rigth on this point 'dynamic sql is not precomplied'AhmedWed, 21 Nov 2007 03:20:00 GMTSQL_ABDhttp://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxHi Ahmed,When a new database is created for the new client, this stored procedure can be altered.As dynamic sql is not precomplied, I think conditional execution is faster.However, I have not compared the performance of these 2 types of stored procedures.Thanks.SureshWed, 21 Nov 2007 03:11:18 GMTSuresh B.http://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxSorry Suresh,But if you will have more than 4 clients, will you still using conditional execution.Good luckRegards,AhmedWed, 21 Nov 2007 02:55:35 GMTSQL_ABDhttp://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxInstead of dynamic SQL, I recommend conditional execution like this:alter proc Test_SP @dbName SysnameAsif @dbName = 'Database1'beginselect * from [b]Database1[/b].dbo.TableNameendelse if @dbName = 'Database2'beginselect * from [b]Database2[/b].dbo.TableNameendGo Wed, 21 Nov 2007 02:51:54 GMTSuresh B.http://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxHi,I think you can use dynamic SQLalter proc Test_SP @dbName SysnameAs Declare @strSQL Nvarchar(200) set @strSQL ='Select * FROM ' + @dbName + '.dbo.orders' exec sp_executesql @strsqlGoRegards,AhmedWed, 21 Nov 2007 02:35:35 GMTSQL_ABDhttp://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxThanks Michael, it looks like this will indeed solve the issue.One question though: you say you "don't advocate adding procedures to the Master database". Can there be serious problems doing so? I never touched the master database before, so I'm not an expert in this area....Thanks again,.RaymondWed, 21 Nov 2007 02:06:09 GMTr.vanlaakehttp://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxI don't advocate adding procedures to the Master database, but this will solve your issue.If you add a procedure to the Master database (and the dbo schema) and use a name starting with "sp_", it will be found when executing it from another database but run in the context of the database it is called from.For example:USE MasterGOCREATE PROCEDURE dbo.sp_TestMeASSELECT DB_Name()GOUSE MyDatabase --Replace with your database nameGOEXEC sp_TestMeTue, 20 Nov 2007 13:32:04 GMTMichael Earl-395764http://www.sqlservercentral.com/Forums/Topic424172-149-1.aspxHi,I have four clients, each with their own identical database. They all have the same Stored Procedure named sp_Test.To simplify things, I want a new database with sp_Test, that is called by the clients.So,I made a new database called COMMON, and put sp_test in there. Problem is, this Stored Procedure doesn't know on which database to perform its actions. A statement like USE DB_CLIENT_ONE gives an error ("USE database statement is not allowed in a procedure").It's annoying that such a Stored Procedure can do the trick, for example:SELECT * FROM DB_CLIENT_ONE.DBO.orders this worksUSE DB_CLIENT_ONESELECT * FROM orders this doesn't work Is there a way to get it done, other than with the EXEC statement?Thanks,Tue, 20 Nov 2007 08:52:28 GMTR. van Laake