SQL

/*==============================================================================================================================================
SP NAME : DBO.UP_FORWARD_COMPANY_INSERT
CONTENT : 견인 업체 코드 입력

입력값 :
@NAME - 견인 업체 명
@CEO_NAME - CEO 명
@ADDR - 견입 업체 주소

최초 작성일 : 2008.05.20
최종 수정일 : 2008.07.04

수정 내역 : (COMPLETE) 2008.05.20 이형규 CREATE
(COMPLETE) 2008.07.04 이형규 TB_FORWARD_COMPANY_CODE --> TB_FORWARD_COMPANY 테이블 명 변경에 따른 기본 스키마 수정
(NEED) 2008.07.04 이형규 각 컬럼값 VALIDATE 할 필요 있음

실행 예제 :

BEGIN TRAN
-- EXEC DBO.UP_FORWARD_COMPANY_INSERT @NAME = 'TEST1'
-- EXEC DBO.UP_FORWARD_COMPANY_INSERT @NAME = 'TEST1', @CEO_NAME = 'TEST2'
-- EXEC DBO.UP_FORWARD_COMPANY_INSERT @NAME = 'TEST1', @CEO_NAME = 'TEST2', @ADDR = 'TEST3'
-- EXEC DBO.UP_FORWARD_COMPANY_INSERT @NAME = 'TEST1', @CEO_NAME = 'TEST2', @ADDR = 'TEST3', @TEL = '11', @DEBUG = 'Y'
-- EXEC DBO.UP_FORWARD_COMPANY_INSERT @NAME = 'TEST1', @CEO_NAME = 'TEST2', @ADDR = 'TEST3', @TEL = '11', @KIND_CODE = 841, @DEBUG = 'Y'

-- 839 : 업체 / 840 : 구청 / 841 : 경찰

SELECT *
FROM TB_FORWARD_COMPANY

ROLLBACK
COMMIT

SELECT *
FROM TB_CODE

==============================================================================================================================================*/

ALTER PROC [dbo].[UP_FORWARD_COMPANY_INSERT]
(
@NAME NVARCHAR(100)
, @CEO_NAME NVARCHAR(100)
, @ADDR NVARCHAR(100)
, @TEL VARCHAR(20)
, @KIND_CODE INT = -1
, @DEBUG CHAR(1) = 'N'
)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @V_QUERY NVARCHAR(3000)
DECLARE @V_INSERT NVARCHAR(1000)
DECLARE @V_VALUES NVARCHAR(1000)

/* [DEFAULT SCHEMA]

BEGIN TRAN

INSERT DBO.TB_FORWARD_COMPANY(NAME, CEO_NAME, ADDR)
VALUES ('TEST1', 'TEST2', 'TEST3')

SELECT *
FROM DBO.TB_FORWARD_COMPANY

ROLLBACK

*/

IF(@KIND_CODE <> -1)
BEGIN
IF NOT EXISTS
(
SELECT CODE
FROM DBO.TB_CODE
WHERE END_DATE IS NULL
AND CODE = @KIND_CODE
AND KIND = 'FORWARD_COMPANY'
)
BEGIN
RAISERROR('존재하지 않는 견인 업체 구분 코드(@KIND)입니다.', 16, 1)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
RETURN 999
END
END
ELSE
BEGIN
RAISERROR('견인 업체 구분 코드(@KIND)는 반드시 입력되어야 합니다. ', 16, 1)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
RETURN 999
END

SET @V_INSERT = ' INSERT DBO.TB_FORWARD_COMPANY ( NAME , CEO_NAME , ADDR, TEL, KIND_CODE )'
SET @V_VALUES = ' VALUES ( ''' + @NAME + ''' , ''' + @CEO_NAME + ''' , ''' + @ADDR + ''', ''' + @TEL + ''', ' + CAST(@KIND_CODE AS VARCHAR(10)) + ')'

/=========================================================================================================================/

SET @V_QUERY = @V_INSERT + @V_VALUES

IF (@DEBUG = 'Y')
BEGIN
PRINT @V_QUERY
END

EXEC SP_EXECUTESQL @V_QUERY

SET TRANSACTION ISOLATION LEVEL READ COMMITTED