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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AmitJain001, do you still need help with this question?
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.