MS SQL.Select-CRUD 문장 자동 만들어주기

(DB) MS SQL/SQL-Select 2017. 2. 9. 17:05

[ MS SQL.Select-CRUD 문장 자동 만들어주기 ]

--*******************************************************************
declare  @tbl_nm                      nvarchar(150);
set      @tbl_nm = 'DO_HDR';
--*******************************************************************
--1. INSERT
SELECT AA.*
  FROM (
                --SELECT 'IF (@ROW_STS = ''I'')'      AS COLUMN_NAME, 'A' AS GUBN, -4 AS SEQ UNION ALL
                --SELECT '  BEGIN'                    AS COLUMN_NAME, 'A' AS GUBN, -3 AS SEQ UNION ALL
                SELECT '    INSERT INTO ' + @tbl_nm AS COLUMN_NAME, 'A' AS GUBN, -2 AS SEQ UNION ALL
                SELECT '             ( '                AS COLUMN_NAME, 'A' AS GUBN, -1 AS SEQ UNION ALL
                SELECT (CASE WHEN A.ORDINAL_POSITION = 1 
                             THEN '               ' 
                             ELSE '             , '   
                        END) + A.COLUMN_NAME, 'A' AS GUBN, A.ORDINAL_POSITION AS SEQ
                  FROM INFORMATION_SCHEMA.COLUMNS A
                 WHERE A.TABLE_NAME IN(@tbl_nm)                                            UNION ALL
                SELECT '      )'                   AS COLUMN_NAME, 'A' AS GUBN, 999 AS SEQ
           ) AS AA
  WHERE 1=1
  UNION ALL
 SELECT BB.*
   FROM (
                SELECT '    VALUES'      AS COLUMN_NAME, 'B' AS GUBN, -4 AS SEQ UNION ALL
                SELECT '             ( '     AS COLUMN_NAME, 'B' AS GUBN, -3 AS SEQ UNION ALL
                SELECT (CASE WHEN A.ORDINAL_POSITION = 1 
                             THEN '               '
                             ELSE '             , '   
                        END) + (CASE WHEN A.COLUMN_NAME = 'SaveDate'   THEN 'dbo.GET_USDate()'
                                     WHEN A.COLUMN_NAME = 'UpdateDate' THEN 'dbo.GET_USDate()'
                                     ELSE '@' +A.COLUMN_NAME
                                END)   , 'B' AS GUBN, A.ORDINAL_POSITION AS SEQ
                  FROM INFORMATION_SCHEMA.COLUMNS A
                 WHERE A.TABLE_NAME IN(@tbl_nm)                                 UNION ALL
                SELECT '                   ) ;'    AS COLUMN_NAME, 'B' AS GUBN, 999 AS SEQ
           ) AS BB
ORDER BY GUBN, SEQ
;

--2. UPDATE
--SELECT 'ELSE IF (@ROW_STS = ''U'')'  , -4 AS SEQ UNION ALL
SELECT '    UPDATE ' + @tbl_nm       , -3 AS SEQ UNION ALL
SELECT '       SET '                 , -2 AS SEQ UNION ALL
SELECT (CASE WHEN A.ORDINAL_POSITION = 1 
                             THEN '           ' 
                             ELSE '         , ' 
                        END) + dbo.GET_PADDING(A.COLUMN_NAME, 15) + ' = ' 
                     + (CASE WHEN A.COLUMN_NAME = 'SaveDate'   THEN 'dbo.GET_USDate()'
                                     WHEN A.COLUMN_NAME = 'UpdateDate' THEN 'dbo.GET_USDate()'
                                     ELSE '@' +A.COLUMN_NAME
                                END)
                                     , A.ORDINAL_POSITION AS SEQ
  FROM INFORMATION_SCHEMA.COLUMNS A
 WHERE A.TABLE_NAME IN(@tbl_nm)        
   AND A.COLUMN_NAME NOT IN('SaveUser', 'SaveDate')            UNION ALL
SELECT '     WHERE '                , 999 AS SEQ 
ORDER BY 2
;

--3. DELETE
--SELECT 'ELSE IF (@ROW_STS = ''D'')'  , -4 AS SEQ UNION ALL
SELECT '    DELETE FROM ' + @tbl_nm      , -3 AS SEQ UNION ALL
SELECT '     WHERE '                 , -2 AS SEQ 
ORDER BY 2
;

저작자표시

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MSSQL-Connect By. 조직도 조회 (0)	2017.04.18
MS SQL.Select-Pivot. UnPivot (0)	2017.02.09
MS SQL.Select-java vo자동생성, procedure arg 자동생성 (0)	2017.02.09
MS SQL.SELECT-레코드를 한 컬럼으로 만들기 (0)	2017.02.09