SQL에서 WHILE 문에서 테이블 변수 사용시 이상한 점..

현재 주소 복사
트랙백 주소 복사
방실이님의 글 (4/6/2012 7:16:58 PM) Viewing : 3152

SQL Query 작성시 저의 경우는 커서 대신 테이블 변수를 사용합니다.

커서보다 월등히 빠르죠..^^;

어쨋든 글의 요지는 이게 아니고.

오늘 이상한 점을 알게 되었습니다.

WHILE 문에서 테이블 변수를 선언하게 되면..

상식적으로 볼때 루프문 안에 있기 때문에 루프 안에서 선언된 변수의 수명은 그 루프의 안에서만 유효합니다.

그런데 이놈은 그렇지 않더라고요.

아래 예제를 봅시당.

 DECLARE @Index int
 DECLARE @Cnt int
 DECLARE @TMP TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, UserId uniqueIdentifier, Role varchar(512))
 INSERT INTO @TMP
 SELECT UserId, '' FROM SstUser
 SET @Cnt = @@ROWCOUNT
 SET @Index = 1

 WHILE @Index <= @Cnt
 BEGIN
  DECLARE @UserId uniqueIdentifier
  SELECT @UserId = UserId FROM @TMP WHERE ID = @Index

  DECLARE @I INT
  DECLARE @C INT
  DECLARE @R VARCHAR(128)
  DECLARE @T TABLE (Id int IDENTITY(1,1) PRIMARY KEY, RoleName varchar(128))
  
  INSERT INTO @T
  SELECT B.RoleName FROM AccountsUsersInRoles A 
   JOIN AccountsRoles B ON A.RoleId = B.RoleId
  WHERE UserId = @UserId
  ORDER BY RoleName
  
  SET @C = @@ROWCOUNT
  SET @I = 1 
  SET @R = ''
  
  WHILE (@I < @C)
  BEGIN
   DECLARE @RoleName varchar(64)
   SELECT @RoleName = RoleName FROM @T WHERE Id = @I
   IF @R <> ''
    SET @R = @R + ','
   SET @R = @R + @RoleName
   SET @I = @I + 1
  END
  
  UPDATE @TMP
  SET Role = @R
  WHERE ID = @Index
  
  SET @Index = @Index + 1
 END

 SELECT A.*, b.Role FROM SstUser A JOIN @TMP B ON A.UserId = B.UserId
 ORDER BY Name
END

9행에 WHILE문을 사용하고 17행을 보면 DECLARE로 테이블 변수를 while 문 내부에서 선언합니다.

이 테이블 변수 @T의 경우 While문 안에서만 유효해야 하는데 실행해 보면..계속 살아 있습니다..^^;;

identity가 적용 되어 있으므로 첫 행의 id는 1이고 다음 루프에서도 id는 1이어야 되는데..그냥 append 됩니다..

상식적으로 이해는 안되나.. 구글링 해보니..원래 그렇더군요..쿨럭..

왜 그럴까요....왜 이렇게 만들었을까요..궁금할 따름이네요..^^;

어쨋든 위 코드는 정상적으로 구동이 되지 않습니다.

아래 처럼 바꿔야 합니다.

 DECLARE @Index int
 DECLARE @Cnt int
 DECLARE @TMP TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, UserId uniqueIdentifier, Role varchar(512))
 INSERT INTO @TMP
 SELECT UserId, '' FROM SstUser
 SET @Cnt = @@ROWCOUNT
 SET @Index = 1

 DECLARE @I INT
 DECLARE @C INT
 DECLARE @R VARCHAR(128)
 DECLARE @T TABLE (Id int IDENTITY(1,1) PRIMARY KEY, RoleName varchar(128))
 SET @I = 1 

 WHILE @Index <= @Cnt
 BEGIN
  DECLARE @UserId uniqueIdentifier
  SELECT @UserId = UserId FROM @TMP WHERE ID = @Index
  
  INSERT INTO @T
  SELECT B.RoleName FROM AccountsUsersInRoles A 
   JOIN AccountsRoles B ON A.RoleId = B.RoleId
  WHERE UserId = @UserId
  ORDER BY RoleName
  
  SET @C = @I + @@ROWCOUNT
  SET @R = ''
  
  WHILE (@I < @C)
  BEGIN
   DECLARE @RoleName varchar(64)
   SELECT @RoleName = RoleName FROM @T WHERE Id = @I
   IF @R <> ''
    SET @R = @R + ','
   SET @R = @R + @RoleName
   SET @I = @I + 1
  END
  
  UPDATE @TMP
  SET Role = @R
  WHERE ID = @Index
  
  SET @Index = @Index + 1
 END

 SELECT A.*, b.Role FROM SstUser A JOIN @TMP B ON A.UserId = B.UserId
 ORDER BY Name

마지막 업데이트 : (4/6/2012 7:17:57 PM)

TAG : SQL 



Trackback 보기 (0)
댓글 보기 (0)
댓글 쓰기