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
CREATE PROCEDURE dbo.[My_Proc]
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