하드코딩이 싫어요

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: 컬럼명

Integer는 null 입력이 가능하다.

Integer 변수에 null이 입력되었을 경우

if문으로 숫자와 비교했을 때 바른 문법이라고 생각했다.

그러나, Integer에 null이 입력했을 경우 숫자와 비교했을 때 아래와 같은 에러가 발생하였다.

습관적으로

NullPointerException이 발생하여 num1에 null이 입력되어 발생하는 줄로만 인식하였다.

그렇다면, 처음 변수 선언하는곳에서 에러가 발생해야 하는게 아닌가?

Integer 변수에 null이 입력되어 발생하는게 아니라,

null과 정수(int 자료형)를 비교하려고 하기 때문에 발생하는 에러였다.

아래를 살펴보자.

정수와 null을 비교해보자. 아예 int와 null 타입자체를 비교할 수 없다고 에러가 발생한다.

습관은 무서운 것이다.

항상, 한 발 물러서서 접근하는 자세가 필요하다.

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