HOw to use different database based on different server

Question

Hi there, in store procedure, the first line usually is to use the database , such as USE [xxxxxx]. I have a situation where the database select should be based on the server name at run time. I'd like to acheive something like below

DECLARE @stageDB varchar(10) = 'xxxxco'
DECLARE @prodDB  varchar(10) = 'xxxxca'
IF @@SERVERNAME = 'STGDB2008'
    use  @stageDB
IF @@SERVERNAME = 'SQLPROD2\SQLPROD2'  
   use @prodDB

I've tried but it failed. Can anyone tell me what's wrong with the code?

Thanks

Hui

---

--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Answer 1

A use statement is not usually the first statement in a stored proc - and if it is you are doing something weird.

In any event, you can't use a variable with a USE statement

---

Chuck

Answer 2

Can we call different sp depending on @@SERVERNAME.

Answer 3

Hello Hui,

I agree with Chuck here. The USE [database] command is NEVER the first line inside a stored procedure.

The logic that you describe above does not belong in the database tier (TSQL code). The logic on deciding which database to connect to, depending on the server name - has to reside in the application tier. This code in the application tier will then make call to the appropriate database depending on the server name. your application tier can be a simple VB app, or a completed ASP.NET application. how you implement this logic in the application tier really depends on the specifics of the app.

Hope this helps!

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 4

Keeping in mind that the other advice you've gotten here is correct, another approach (not exactly the same thing, and still subject to problems or even disaster if you forget to keep things in order), would be to define SYNONYMS (Create SYNONYM ...) for both environments and use those synonyms in your proc(s). 

The procs would not change on the fly: What would change for each environment (during setup/configuration, not for every run of the proc), would be creating the synonyms to point to the desired database and table.  The catch to this is depending on a manual process and human memory to avoid mixups.  It will happen!

A safeguard I'd recommend would be to NOT link the stage and prod servers, to avoid disaster should stage accidentally point to production, or vice versa.  An error message is better than corrupting data.

Answer 5

The more I think about this one, the more I'm thinking that the original poster is talking about the defaults script generation to create stored procs.  IF that is the case then just turn off the USE statement generation in the scripting options.

Example:

USE [TestJunk]
GO

/****** Object:  StoredProcedure [dbo].[doDBEmpty]    Script Date: 5/1/2012 7:10:20 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE blah
.....

---

Chuck

Answer 6

Thanks all for these professional suggestions!

Hui

---

--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --