본문 바로가기
프로그래밍/MS-SQL

[MS-SQL] CRUD 자동 스크립트 작성 내용 공유

고특파원 2019. 7. 12. 18:58

해당 SQL스크립트는 MSSQL 저장프로시저를 자동으로 생성하는데 도움을 주는 프로시저이다.

프로그램을 개발하면서 가장 기본이되는 작업은 데이터를 읽고, 입력하고, 수정하고, 삭제하는 작업이다.

 

프로그램을 개발하다보면, 반복적으로 CRUD SQL문을 작성하는 경우가 많다.

시간을 어느정도 절약 할 수 있도록 공유된 프로시저를 이용하면 기본 SELECT, INSERT, UPDATE, DELETE 문을 만들어주기에 개발속도 및 개발 효율성을 증가 시킬 수 있다. (물론 초보자라면 직접 타이핑하는게 좋겠지만, 프로시저에 대한 완벽한 이해가 되어있으면 사용하는게 좋을듯 함.)

 

사용방법에 대해 간단히 소개한다.

 

물론 SqlServerManagementStudio에서 사용을 하는 것이며, 사용을 하기 위한 데이터베이스에 아래 프로시저를 생성한다.

 

-- EXEC [dbo].ZZZ.PROCEDURE_CREATOR '첫번째파라미터','두번째파라미터','세번째파라미터'

 

첫번째파라미터 => Database 테이블명

두번째파라미터 => 프로시저 생성자이름

세번째파라미터 => 프로시저에 대한 설명 공통문

 

 

아래 해당 프로시저를 보면 자신이 원하는 입맛대로 수정도 가능할듯 하니, 필요한사람은 복사해서 마음껏 가져다 쓰시고, 이 포스팅에 대한 공감하나 눌러주고 가시길..!

 

CRUD 자동 스크립트

-- ============================================= 
-- AUTHOR:   ProSungsiler
-- CREATE DATE: 2019-06-17
-- DESCRIPTION: 프로시저 스크립트 자동생성 
--				@P_TABLE_NAME	: DB 테이블 명
--				@P_CREATOR		: 프로시저 생성자 이름
--				@P_DESCRIPTION	: 주석 및 프로시저 설명
-- ============================================= 
-- EXEC [dbo].ZZZ_PROCEDURE_CREATOR 'PR_EMPLOYEE','ProSungsiler','기본코드'
CREATE PROCEDURE [dbo].[ZZZ_PROCEDURE_CREATOR]
	  
	 @P_TABLE_NAME	VARCHAR(50)		
	,@P_CREATOR		VARCHAR(50)		
	,@P_DESCRIPTION	VARCHAR(200)	
AS  
BEGIN  

	
	DECLARE @INDEX INT

	-- ===================컬럼정보======================== 
	DECLARE @P_COLUMN_NAME						VARCHAR(256)
	DECLARE @P_IS_NULLABLE						VARCHAR(256)
	DECLARE @P_DATA_TYPE						VARCHAR(256)
	DECLARE @P_CHARACTER_MAXIMUM_LENGTH			INT
	DECLARE @P_NUMERIC_PRECISION				INT
	DECLARE @P_NUMERIC_SCALE					INT
	DECLARE @P_PK_YN							VARCHAR(1)
	DECLARE @ORD_NO								INT
	-- ===================컬럼정보========================
	
	--SELECT 
	--	 *
	--FROM INFORMATION_SCHEMA.COLUMNS
	--WHERE TABLE_NAME = @P_TABLE_NAME
	--ORDER BY ORDINAL_POSITION ASC

	

	/* =====================================================
	-- C) 기본 프로시저 생성 
	-- =================================================== */
	PRINT('GO')
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + ' 생성')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_C]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_C]' + CHAR(13))
	
	-- ==================================================================
	-- ===================1. 테이블 파라미터 정의======================== 

		DECLARE MYCUR CURSOR FOR
		SELECT 
			 COLUMN_NAME				-- 컬럼이름
			,IS_NULLABLE				-- NULL값 여부
			,DATA_TYPE					-- 데이터형식
			,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
			,NUMERIC_PRECISION			-- 숫자 정수부분
			,NUMERIC_SCALE				-- 숫자 소수부분
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @P_TABLE_NAME
		ORDER BY ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
			SET @INDEX = @INDEX + 1

			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR
			
	-- ===================1. 테이블 파라미터 정의======================== 
	-- ==================================================================

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))
	
	-- ==================================================================
	-- ===================2. 테이블 컬럼 정의======================== 

	PRINT('		INSERT INTO ' + @P_TABLE_NAME)
	PRINT('		(')
	
	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 '+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR

	PRINT('		)')
	PRINT('		VALUES')
	PRINT('		(')
	
	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 @P_'+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,@P_'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR


	PRINT('		)')
	PRINT(CHAR(13))
	
	-- ===================2. 테이블 컬럼 정의======================== 
	-- ==================================================================
	PRINT('END')







	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- R) 기본 프로시저 조회 
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '조회')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_R]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_R]' + CHAR(13))

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))

	PRINT('		SELECT')
	
	-- ==================================================================
	-- ===================1. 테이블 컬럼 정의======================== 

	DECLARE MYCUR CURSOR FOR
	SELECT 
		 COLUMN_NAME				-- 컬럼이름
		,IS_NULLABLE				-- NULL값 여부
		,DATA_TYPE					-- 데이터형식
		,CHARACTER_MAXIMUM_LENGTH	-- 문자열 길이 
		,NUMERIC_PRECISION			-- 숫자 정수부분
		,NUMERIC_SCALE				-- 숫자 소수부분
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @P_TABLE_NAME
	ORDER BY ORDINAL_POSITION ASC

	SET @INDEX = 0

	OPEN MYCUR

	FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE
	WHILE (@@FETCH_STATUS=0)
	BEGIN      
		
		IF(@INDEX = 0)
		BEGIN
			PRINT('			 '+ @P_COLUMN_NAME)
		END
		ELSE
		BEGIN
			PRINT('			,'+ @P_COLUMN_NAME)
		END
		
		SET @INDEX = @INDEX + 1

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE

	END

	CLOSE MYCUR

	DEALLOCATE MYCUR

	-- ===================1. 테이블 컬럼 정의======================== 
	-- ==================================================================
	

	PRINT('		FROM ' + @P_TABLE_NAME) 


	PRINT(CHAR(13))
	PRINT('END')


	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- U) 기본 프로시저 수정
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '수정')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_U]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_U]' + CHAR(13))

	-- ==================================================================
	-- ===================1. 테이블 파라미터 정의======================== 

		DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
			SET @INDEX = @INDEX + 1

			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR
			
	-- ===================1. 테이블 파라미터 정의======================== 
	-- ==================================================================

	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))

	PRINT('		UPDATE ' + @P_TABLE_NAME)
	PRINT('		SET')
	-- ==================================================================
	-- ===================2. 테이블 업데이트 내역 정의===================
	 
	 DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'N')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('			 '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('			,'+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1	
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================2. 테이블 업데이트 내역 정의===================
	-- ================================================================== 

	
	-- ==================================================================
	-- ===================3. 테이블 업데이트 기본키 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('		WHERE '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('		  AND '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1
					
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================3. 테이블 업데이트 기본키 내역 정의===================
	-- =========================================================================

	PRINT(CHAR(13))
	PRINT('END')







	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT(CHAR(13))
	PRINT('GO')
	/* =====================================================
	-- D) 기본 프로시저 수정
	-- =================================================== */
	PRINT('-- ============================================= ')
	PRINT('-- AUTHOR: '+ @P_CREATOR)
	PRINT('-- CREATE DATE: ' + CONVERT(VARCHAR,GETDATE(),23))
	PRINT('-- DESCRIPTION: ' + @P_DESCRIPTION + '삭제')
	PRINT('-- ============================================= ')
	PRINT('-- EXEC [USP_'+ @P_TABLE_NAME +'_D]')
	PRINT('CREATE PROCEDURE [dbo].[USP_'+ @P_TABLE_NAME +'_D]' + CHAR(13))

	-- =========================================================================
	-- ===================1. 파라미터 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				
				IF(UPPER(@P_DATA_TYPE) = 'VARCHAR' OR UPPER(@P_DATA_TYPE) = 'CHAR')
				BEGIN
					-- 일반문자열
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_CHARACTER_MAXIMUM_LENGTH)+')')
					END
					
				END
				ELSE IF
					 ( UPPER(@P_DATA_TYPE) = 'NUMERIC' 
					OR UPPER(@P_DATA_TYPE) = 'DECIMAL'
					 )
				BEGIN
					-- 숫자
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE) + '('+CONVERT(VARCHAR,@P_NUMERIC_PRECISION)+','+ CONVERT(VARCHAR,@P_NUMERIC_SCALE) +')')
					END
				END
				ELSE
				BEGIN
					-- 그외
					IF(@INDEX = 0)
					BEGIN
						PRINT('		@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
					ELSE
					BEGIN
						PRINT('	   ,@P_'+ @P_COLUMN_NAME + '					' + UPPER(@P_DATA_TYPE))
					END
				END
			
				SET @INDEX = @INDEX + 1

			END

			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================1. 파라미터 내역 정의===================
	-- =========================================================================


	PRINT(CHAR(13)+'AS')
	PRINT('BEGIN')
	PRINT(CHAR(13))
	
	PRINT('		DELETE FROM ' + @P_TABLE_NAME)
	-- ==================================================================
	-- ===================2. 테이블 삭제 기본키 내역 정의===================
	
	DECLARE MYCUR CURSOR FOR
		SELECT DISTINCT
			 A.COLUMN_NAME					-- 컬럼이름
			,A.IS_NULLABLE					-- NULL값 여부
			,A.DATA_TYPE					-- 데이터형식
			,A.CHARACTER_MAXIMUM_LENGTH		-- 문자열 길이 
			,A.NUMERIC_PRECISION			-- 숫자 정수부분
			,A.NUMERIC_SCALE				-- 숫자 소수부분
			,CASE WHEN ISNULL(B.COLUMN_NAME,'') = '' THEN 'N' ELSE 'Y' END	PK_YN	-- 기본키항목값을 가져오면 기본키
			,A.ORDINAL_POSITION
		FROM INFORMATION_SCHEMA.COLUMNS A
		LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		  ON B.TABLE_NAME = A.TABLE_NAME
		 AND B.COLUMN_NAME = A.COLUMN_NAME
		WHERE A.TABLE_NAME = @P_TABLE_NAME
		ORDER BY A.ORDINAL_POSITION ASC

		SET @INDEX = 0

		OPEN MYCUR

		FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO
		WHILE (@@FETCH_STATUS=0)
		BEGIN      
			
			IF(@P_PK_YN = 'Y')
			BEGIN
				IF(@INDEX = 0)
				BEGIN
					PRINT('		WHERE '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				ELSE
				BEGIN
					PRINT('		  AND '+@P_COLUMN_NAME + ' = @P_' + @P_COLUMN_NAME)
				END
				
				SET @INDEX = @INDEX + 1
					
			END
			
			FETCH NEXT FROM MYCUR INTO @P_COLUMN_NAME, @P_IS_NULLABLE, @P_DATA_TYPE, @P_CHARACTER_MAXIMUM_LENGTH, @P_NUMERIC_PRECISION, @P_NUMERIC_SCALE, @P_PK_YN, @ORD_NO

		END

		CLOSE MYCUR

		DEALLOCATE MYCUR

	-- ===================2. 테이블 삭제 기본키 내역 정의===================
	-- =========================================================================


	PRINT(CHAR(13))
	PRINT('END')


END

 

 

저작자표시

'프로그래밍 > MS-SQL' 카테고리의 다른 글

MSSQL 함수 작성법  (0) 2019.08.06
MSSQL 저장프로시저(StoredProcedure) 작성법  (0) 2019.07.30
MSSQL 커서(CURSOR) 사용법  (0) 2019.07.30
윈도우 서버 MSSQL 외부접속 설정하기  (0) 2019.07.01
[MSSQL] AdventureWorks Sample Database 사용법  (0) 2016.01.30

'프로그래밍/MS-SQL' Related Articles

티스토리툴바