CREATE PROC (ALTER PROC사용하지 않는 방법)

1. Why?

PROCEDURE쿼리를 짤때, CREATE PROC을 한 후 쿼리를 수정할 경우 ALTER PROC을 통해 쿼리를 수정해야하는 번거로움이 발생한다.
이에 IF, DROP PROC을 이용해 쿼리작성을 더 효율적으로 할 수 있다.

2. Format

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'test1')
    DROP PROC test1
GO

CREATE PROC test1
	@ = ''
   ,@ = ''
AS
    SELECT 
      FROM 
     WHERE

RETURN
GO

EXEC test1 @ = ''
		  ,@ = ''
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'test2')
    DROP PROC test2
GO

CREATE PROC test2
	@ = ''
   ,@ = ''
AS
    SELECT 
      FROM 
     WHERE

RETURN
GO

EXEC test2 @ = ''
		  ,@ = ''

3. 예

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Master01_SWEduTEST_1')
    DROP PROC Master01_SWEduTEST_1
GO

CREATE PROC Master01_SWEduTEST_1
    @CompanySeq     INT,
    @SalesYM        NCHAR(4)
AS
    SELECT A.SalesNo, A.SalesDate, C.ItemName, B.Qty, B.DomAmt
      FROM _TSLSales AS A WITH(NOLOCK)
           JOIN _TSLSalesItem AS B WITH(NOLOCK) ON A.CompanySeq = B.CompanySeq
                                               AND A.SalesSeq   = B.SalesSeq
           JOIN _TDAItem      AS C WITH(NOLOCK) ON A.CompanySeq = C.CompanySeq
                                               AND B.ItemSeq    = C.ItemSeq
     WHERE A.CompanySeq = @CompanySeq
       AND A.SalesDate LIKE @SalesYM + '%'
       AND (B.Qty < 0 OR B.DomAmt < 0)

RETURN
GO

EXEC Master01_SWEduTEST_1 @CompanySeq = 2, @SalesYM = '2020'
GO







IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Master01_SWEduTEST_2')
    DROP PROC Master01_SWEduTEST_2
GO
CREATE PROC Master01_SWEduTEST_2
    @CompanySeq     INT,
    @SalesYM        NCHAR(4)
AS

    SELECT C.ItemName,
           SUM(B.Qty) AS '수량합',
           SUM(B.DomAmt) AS '금액합',
           SUM(B.DomAmt) / CASE WHEN SUM(B.Qty) <> 0 THEN SUM(B.Qty) ELSE 1 END AS '금액합단가',
           MIN(B.ItemPrice) AS '최저단가',
           MAX(B.ItemPrice) AS '최고가',
           AVG(B.ItemPrice) AS '평균가',
           COUNT(1) AS '주문건수'
    
      FROM _TSLSales AS A WITH(NOLOCK)
           JOIN _TSLSalesItem AS B WITH(NOLOCK) ON A.CompanySeq = B.CompanySeq
                                               AND A.SalesSeq   = B.SalesSeq
           JOIN _TDAItem AS C WITH(NOLOCK) ON A.CompanySeq = C.CompanySeq
                                               AND B.ItemSeq    = C.ItemSeq
     WHERE A.CompanySeq = @CompanySeq
       AND A.SalesDate LIKE @SalesYM + '%'
     GROUP BY C.ItemName
     ORDER BY '금액합' DESC

RETURN
GO
EXEC Master01_SWEduTEST_2 @CompanySeq = 2, @SalesYM = '2020'

4. 자세히 보기

SELECT *
  FROM sysobjects;

sysobjects상 Master01_SWEduTEST_1가 존재하면 해당 PROC을 매번 실행할때 마다DROP PROC하므로, ALTER PROC가 아닌 CREATE PROC을 통해 쿼리를 작성할 수 있다.