[MSSQL] DB프로시저

▼ 환경설정

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET XACT_ABOUT ON --

 

 

▼ 변수 선언/기본값 지정

DECLARE @변수명 VARCHAR(10); 
-- 선언 
SET @변수명 = 'abcde'; 
-- 값 설정1 
SET @변수명 = ColumnName FROM  TableName WHERE Codition = 1; 
-- 값 설정2  
DECLARE @변수명 VARCHAR(10) = 'abcde'; -- 선언&기본값 

▼ CURSOR 사용 예

SET @DATA_CUR = CURSOR 
FAST_FORWARD 
FOR ( 
	SELECT DeviceID 
	FROM DEVICE_01 A 
	WHERE DeviceType NOT IN (SELECT ModelType FROM MODEL WITH(NOLOCK)) 
) 
OPEN @DATA_CUR 
FETCH NEXT FROM @DATA_CUR INTO @DeviceID 
WHILE @@FETCH_STATUS = 0 
BEGIN 
	UPDATE DEVICE_02 SET UpdateDate = (CONVERT(char(8),GETDATE(),(112))) WHERE DeviceID = @DeviceID 
	
	IF @@ROWCOUNT = 0 
		INSERT INTO dbo.DEVICE_ERROR ( DeviceID,DeviceName, Addr ,DeviceType,DeviceIP,CreateDate,UpdateDate ) 
		SELECT DeviceID,DeviceName, Addr ,DeviceType,DeviceIP,CreateDate,UpdateDate 
          FROM DEVICE_02 
         WHERE devicenum = @devicenum 

	DELETE DEVICE_01 
     WHERE DeviceID = @DeviceID 

FETCH NEXT FROM @DATA_CUR INTO @DeviceID 
END CLOSE @DATA_CUR