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
AmitJain001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
in sql server you can create procedures trying to use databases/tables/views that do not exist (at that time)
https://technet.microsoft.com/en-us/library/ms190686%28v=sql.90%29.aspx

now, what do you want to happen? not creating the procedure at all? in that case you will need some "intelligent" deployment tool to create things only when the requirements are there.

please clarify
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
AmitJain001, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.