Saturday, January 21, 2017

How to Check whether If a Stored Procedure already exists or not in SQL Server

SQL Server IF Condition to Check whether a SP or Stored Procedure is already Exists or not
Use TAMATAM
GO
IF OBJECT_ID('MyProc, 'P') IS NOT NULL
DROP PROCEDURE MyProc;
GO
 

--------- OR ---------------
IF EXISTS ( Select  *
            FROM    Sys.Objects
            WHERE   Object_Id = OBJECT_ID(N'MyProc')
And Type IN ( N'P', N'PC' ) ) 
BEGIN
--DROP PROC MyProc
PRINT 'The Stored Procedure Found and its Dropped'
END
ELSE
BEGIN
PRINT 'The Stored Procedure Doesnot Exist'
END
GO

--Creating the Stored Procedure
Create Proc MyProc as 
Begin
 Select* Into MyTable from (
 Select P.Prod_Id,P.Prod_Name,P.Sales_Region,Sales_Period,
            C.Cust_Id,C.Cust_Name,C.Cust_Location 
   From Products P 
Left JOIN
Customers C
ON P.Cust_Id=C.Cust_Id WHERE C.Cust_Id is null ) S1 
Update MyTable SET Cust_Id='NewCustomer' where Cust_id is null
SELECT* FROM MyTable

PRINT 'The Stored Procedure SuccessFully Created'
End
GO

--Example of how to do it when including the Schema:

IF EXISTS ( SELECT * 
            FROM   Sysobjects 
            WHERE  Id = Object_Id(N'[dbo].[MyProc]') 
                           And OBJECTPROPERTY(Id, N'IsProcedure') = 1 )
BEGIN
--DROP PROC MyProc
PRINT 'The Stored Procedure Found and its Dropped'
END
ELSE
BEGIN
PRINT 'The Stored Procedure Doesnot Exist'
END
GO
--Creating the Stored Procedure
Create Proc MyProc as 
Select * Into MyTable from (

SELECT P.Prod_Id,P.Prod_Name,P.Sales_Region,Sales_Period,
                C.Cust_Id,C.Cust_Name,C.Cust_Location 
   FROM Products P 
LEFT JOIN
Customers C
ON P.Cust_Id=C.Cust_Id WHERE C.Cust_Id is null ) S1 
Update MyTable SET Cust_Id='NewCustomer' where Cust_id is null
Select* From MyTable

PRINT 'The Stored Procedure SuccessFully Created'
GO

-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog