Link to home
Start Free TrialLog in
Avatar of AmitJain001
AmitJain001

asked on

How Stirct checking the database name while Creating Stored Procedure.

Hi Experts,

I have written a stored Procedures which uses multiple database, It works fine on my local environment, now when I execute the same code on UAT server to create the procedure; it gets created successfully despite the fact that there is no database  exists on UAT server with the name "Database2".

So, what I want to know is -
  - the way which should first validate whether all the objects and databases exists in the database and database server or not then only the procedure should get created once all the objects exist.


here is my code:

IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'My_Proc' AND type = 'P'))
      DROP PROCEDURE dbo.My_Proc
GO

CREATE PROCEDURE dbo.[My_Proc]
       WITH RECOMPILE
AS
BEGIN

UPDATE  <Database1>.dbo.tbl1
SET     FieldID = tbl2.FieldID
     
 FROM    <Database2>.dbo.tbl2 (NOLOCK)
      INNER JOIN <Database2>.dbo.Tbl3 (NOLOCK) ON Tbl3.ID = tbl2.ID
      INNER JOIN <Database2>.dbo.Tbl4 (NOLOCK) ON Tbl4.ID = tbl2.ID
END
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Another case: What should happen when your databases exist when creating the procedure, but one is removed or renamed later?

The only thing, I can think which is a possibility here: Check the existence of your database(s) in the sproc and when they exist execute your UPDATE as dynamic SQL. This will avoid the runtime error.

Caveat: This is basically error hiding/masking. This is not a proper solution in most cases.
AmitJain001, do you still need help with this question?