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 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 

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

--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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts