하드코딩이 싫어요

USE [DBNAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE PROCEDURENAME
(
	@IN_PARAMETER1 NVARCHAR(10),
	@IN_PARAMETER2 BIGINT
)
AS
	DECLARE @SQL NVARCHAR(4000) = '';
	DECLARE @PARAMDEFINITION NVARCHAR(4000);
    
BEGIN
	SET NOCOUNT ON;
    
	SET @SQL = 
            N'
                SELECT 	* 
                    FROM 	TABLE 
                    WHERE 	IN_PARAMETER1 = @IN_PARAMETER1
                    AND	IN_PARAMETER2 = @IN_PARAMETER2		
            '
        ;

	SET @PARAMDEFINITION = N'@IN_PARAMETER1 NVARCHAR(10), @IN_PARAMETER2 BIGINT';

	EXEC SP_EXECUTESQL @SQL, @PARAMDEFINITION, 
		@IN_PARAMETER1 = @IN_PARAMETER1, @IN_PARAMETER2 = @IN_PARAMETER2
    ;
END

동적 쿼리(Dynamic SQL) 생성 예제

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

USE DBNAME
GO

-- TABLE
CREATE TABLE TABLENAME
(
    COLUMNNAME1 NVARCHAR(10) NOT NULL,
    COLUMNNAME2 INT,
    COLUMNNAME3 DATE DEFAULT GETDATE()
);

-- TABLE COMMENT
EXEC SP_ADDEXTENDEDPROPERTY 'MS_Description', 'Comment', 'USER', dbo, 'TABLE', TABLENAME;

-- COLUMN COMMENT
EXEC SP_ADDEXTENDEDPROPERTY 'MS_Description', 'Comment', 'USER', dbo, 'TABLE', TABLENAME, 'COLUMN', COLUMNNAME;

DBNAME: 데이터베이스 명

TABLENAME: 테이블 명

'Comment': 사용할 커멘트

COLUMNNAME: 컬럼명

WITH TT AS (
    SELECT 10000 AS DEPTCD, '회사' AS DEPTNAME, NULL AS PARENTDEPTCD, 1 AS DEPTH
    UNION ALL
    SELECT 11000 AS DEPTCD, '경영지원실' AS DEPTNAME, 10000 AS PARENTDEPTCD, 2 AS DEPTH
    UNION ALL
    SELECT 12000 AS DEPTCD, '영업지원실' AS DEPTNAME, 10000 AS PARENTDEPTCD, 2 AS DEPTH
    UNION ALL
    SELECT 12100 AS DEPTCD, '영업팀' AS DEPTNAME, 12000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 12200 AS DEPTCD, '영업팀' AS DEPTNAME, 12000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 11100 AS DEPTCD, '경제팀' AS DEPTNAME, 11000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 11200 AS DEPTCD, '경영팀' AS DEPTNAME, 11000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 11210 AS DEPTCD, '총무파트' AS DEPTNAME, 11200 AS PARENTDEPTCD, 4 AS DEPTH
    UNION ALL
    SELECT 11220 AS DEPTCD, '인사파트' AS DEPTNAME, 11200 AS PARENTDEPTCD, 4 AS DEPTH
    UNION ALL
    SELECT 11110 AS DEPTCD, '경제전략1팀' AS DEPTNAME, 11100 AS PARENTDEPTCD, 4 AS DEPTH
    UNION ALL
    SELECT 11120 AS DEPTCD, '경제전략2팀' AS DEPTNAME, 11100 AS PARENTDEPTCD, 4 AS DEPTH
), CTE AS (
    SELECT DEPTCD
        ,  DEPTNAME
        ,  PARENTDEPTCD
        ,  CONVERT(NVARCHAR(300), DEPTNAME) AS FULLPATH
        ,  CONVERT(NVARCHAR(300), DEPTNAME) AS REVERSEFULLPATH
    FROM TT
    WHERE PARENTDEPTCD IS NULL
    UNION ALL
    SELECT B.DEPTCD
        ,  B.DEPTNAME
        ,  B.PARENTDEPTCD
        ,  CONVERT(NVARCHAR(300), CONCAT(C.FULLPATH, ' > ', B.DEPTNAME)) AS FULLPATH
        ,  CONVERT(NVARCHAR(300), CONCAT(B.DEPTNAME, ' > ', C.REVERSEFULLPATH)) AS REVERSEFULLPATH
    FROM TT B, CTE C
    WHERE B.PARENTDEPTCD = C.DEPTCD
)
SELECT * 
FROM CTE

오라클 DB에서 MSSQL로 옮기는 작업을 하는 중인데

차이점을 기록해둘 필요가 있어, 하나씩 작성해보려 한다.

※ 변수선언 및 값 대입

ORACLE
	abc VARCHAR2(10);
	abc VARCHAR2(10) := 1;
	abc := 5;
    

MSSQL
	DECLARE @abc VARCHAR(10);
	DECLARE @abc VARCHAR(10) = 1;
	SET @abc = 5;

※ SELECT 변수 대입

ORACLE
	V_A VARCHAR2(100);
	V_B NUMBER(5);
	NUM_A NUMBER(10);
    
	SELECT COLUMN_A, COLUMN_B
	  INTO V_A, V_B
	  FROM TABLE_NAME
	 WHERE COLUMN_A = 1
	   AND COLUMN_B = NUM_A;
       

MSSQL
	DECLARE @V_A 	VARCHAR(100);
	DECLARE @V_B 	INT;
	DECLARE @NUM_A 	BIGINT;
    
	SELECT @V_A = COLUMN_A
	    ,  @V_B = COLUMN_B
	  FROM TABLE_NAME
	 WHERE COLUMN_A = 1
	   AND COLUMN_B = @NUM_A;

※ 예외 선언

ORACLE
    BEGIN
        -- SQL
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- SQL
        WHEN OTHERS THEN
            -- SQL
        
        
MSSQL
    BEGIN TRY
        -- SQL
        
        -- ORACLE: NO_DATA_FOUND
        IF @@ROWCOUNT = 0 BEGIN
            -- SQL
        END
    END TRY
    BEGIN CATCH
        -- SQL
        -- WHEN OTHERS THEN
    END CATCH

※ 카탈로그 통계 정보 갱신

ORACLE
	DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE_NAME');
    
MSSQL
	UPDATE STATISTICS TABLE_NAME;