▼ 환경설정
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
