[MSSQL] CREATE OR REPLACE PROCEDURE

Postgresql 등 타 DB에서는 Function 스크립트를 만들 때에, 앞 부분에 CREATE OR REPLACE 등의 구문을 사용하여, 해당 Function 이 존재하지 않을 경우에는 생성하고, 존재할 경우에는 REPLACE(ALTER) 할 수 있습니다.

그런데, MSSQL 에서는 이게 안 됩니다. 예전에 사용하던 버전에서도 안 됐고, 혹시나 (그나마) 최신 버전인 2014에서는 지원하려나 했는데, 이게 안 되는 군요.

그리하여, 예전에 사용했던 방법은 다음과 같습니다. OBJECT_ID Function을 통해 해당 프로시저 객체가 있는지 파악하고, 있으면 일단 DROP 먼저 치고 CREATE 하는 방식 입니다.

IF OBJECT_ID('dbo.usp_login', 'P') IS NOT NULL
    DROP PROC dbo.usp_login;
GO

CREATE PROC dbo.usp_login
(
    @user_code          VARCHAR(50)
  , @password           VARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;

    ...

END
GO

설치형 또는 실시간 서비스가 아닌 경우에는 위와 같은 방법을 사용해도 무방하나, 실제 라이브 하고 있는 서비스의 경우에는, 혹시라도 새로 생성할 프로시저 스크립트를 잘못 만들었거나 할 경우, 해당 프로시저는 그냥 날아가(DROP)버리고, 없어지는 문제가 발생할 수 있습니다.

MSSQL 의 경우 프로시저 생성 시 구문 분석이 좀 더 엄격하기 때문에, 프로시저 생성 구문에서 종종 오류를 뱉는 경우를 많이 접하게 됩니다.

이러한 경우에는, 거꾸로 생각해서, 먼저 프로시저 객체가 존재하는지 확인하고, 없으면 그냥 대충 생성하고, 실제 생성할 프로시저 스크립트를 하단에 두면서 ALTER 구문으로 진행하는 것이 좋을 것 입니다.

IF OBJECT_ID('dbo.usp_login', 'P') IS NULL
    EXEC ( 'CREATE PROC dbo.usp_login AS BEGIN SET NOCOUNT ON; END;' );
GO

ALTER PROC dbo.usp_login
(
    @user_code          VARCHAR(50)
  , @password           VARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;

    ...

END
GO

이와 같은 방식은 Postgresql Function 생성 등에는 맞지 않습니다. Postgresql 등 에서는 Function Overloading 을 지원하기 때문에 앞서 생성한 것과 뒤에 수정본은 서로 다른 Function 으로 인지하게 됩니다. 이 Overloading 지원 때문에 Function 을 DROP 할 때에 파라메터의 형식을 일일이 나열해야 하는 불편함은 물론, 단순히 파라메터의 VARCHAR (Postgresql에서는 Character Varing) 길이를 수정할 경우에도 새로 만들어지는 불편함이 발생하게 됩니다. 하지만, 이게 징징 거릴 것이 못 되는 것이, 서비스 중인 Function의 외부 노출 파라메터 형식이 바뀐다는 건 더 이상한 경우가 되겠지요.

어느 쪽 DB가 더 낫다라고는 아직 판단이 어렵네요.

2016.08.18 코드를 좀 수정했습니다. 어제 작성할 때에는 잘 되던 것 같은데, 오늘 해 보니 안 되네요. CREATE PROC... 구문이 IF 절 내에서는 안 먹는 것으로 보입니다. 동적 쿼리로 수정해야 할 듯 보이네요.