Saturday, 21 January 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 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 Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts