저장프로시저: 2개의 글

MSSQL 저장프로시저(StoredProcedure) 작성법

Posted by 고 고특파원
2019. 7. 30. 19:09 프로그래밍/MS-SQL

MSSQL을 사용하다 보면 흔히 접할 수 있는것 중의 하나가 바로 저장프로시저(Stored Procedure)이다.

 

저장프로시저는 개발자가 자주 쓰는 쿼리문을 하나의 집합단위로 관리하는것을 말한다.

 

프로그램과 DB를 연결하여 데이터를 관리(데이터조회, 삽입, 수정, 삭제) 하는 수단은 반복되는 작업이므로

DB테이블 1개당 저장프로시저를 보통 (C,R,U,D) 4개의 프로시저를 생성하여 관리한다.

 

본론으로 돌아와서, 저장프로시저를 작성하는 방법에 대해 설명하도록 하겠다.

 

 

저장프로시저(StoredProcedure) 작성 예시

USE [DataBase이름]
GO

CREATE PROCEDURE [dbo].[프로지서이름]
	 
	--	@P_PARAMETER1		VARCHAR(10)		-- 파라미터가 필요한경우, 추가해야한다.

AS  
BEGIN  
	
	-- 자주사용하는 SQL 쿼리문
	SELECT
		NULL

END

USE[DataBase이름] => 개발중인 데이터베이스 이름을 입력해서 넣어준다.

GO

 

CREATE PROCCEDURE [dbo].[프로시저이름] => 프로시저이름은 본인이 설정하고 싶은대로 입력하여 넣어준다.

 

파라미터가 필요한 경우,

 

@파라미터변수명    자료형

 

저장프로시저를 통해서 SELECT문, INSERT문, UPDATE문, DELETE문 등 데이터를 조작하는 쿼리를 입력하여 관리할 수 있다.

 

 

저장프로시저(StoredProcedure) 수정 예시

저장프로시저를 생성하고 난 후, 수정을 해야하는 경우는 생성된 저장프로시저를 찾고 간단히 수정을 해주면 된다.

 

CREATE PROCCEDURE [dbo].[프로시저이름] >> ALTER PROCCEDURE [dbo].[프로시저이름]

 

생성예시문과 전부다 동일하고 윗부분만 다르다.

 

저장프로시저 수정 예시

 

아래에는 저장프로시저를 쉽게 만들수 있도록, 자동으로 생성해주는 스크립트 파일 링크를 추가해 놓도록 하겠다.

필요한 경우 가져가서 마음껏 사용 하도록 바란다.

(가져가실때 공감과 감사의표시로 댓글 하나씩만 부탁드립니다.)

 

자세한 내용과 설명은 아래링크에서 확인하시면 됩니다.

 

[프로그래밍/MS-SQL] - [MS-SQL] CRUD 자동 스크립트 작성 내용 공유

 

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

해당 SQL스크립트는 MSSQL 저장프로시저를 자동으로 생성하는데 도움을 주는 프로시저이다. 프로그램을 개발하면서 가장 기본이되는 작업은 데이터를 읽고, 입력하고, 수정하고, 삭제하는 작업이다. 프로그램을 개..

maelife.tistory.com

 
제 블로그의 내용이 도움이 되셨나요?

여러분의 공감댓글이 큰 힘이됩니다 :)

▼▼▼▼▼▼▼▼▼▼▼
이 댓글을 비밀 댓글로

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

Posted by 고 고특파원
2019. 7. 12. 18:58 프로그래밍/MS-SQL

해당 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

 

 

이 댓글을 비밀 댓글로