New

GO
CREATE PROCEDURE usp_while
AS
           DECLARE userCur CURSOR FOR-- 커서 선언
           SELECT U.userid,sum(price*amount)
              FROM buyTbl B
                       RIGHT OUTER JOIN userTbl U
                       ON B.userid = U.userid
            GROUP BY U.userid, U.name

            OPEN userCur  -- 커서열기

            DECLARE @id NVARCHAR(10) -- 사용자아이디를 저장할 변수
            DECLARE @sum BIGINT -- 총구매액을 저장할 변수
            DECLARE @userGrade NCHAR(5) -- 고객 등급 변수

            FETCH NEXT FROM userCur INTO @id, @sum -- 첫행 값을 대입

            WHILE (@@FETCH_STATUS=0) -- 행이 없을 때까지 반복(즉, 모든 행 처리)
            BEGIN
              SET @userGrade =
                CASE 
                     WHEN (@sum >= 1500) THEN N'최우수고객'
                     WHEN (@sum  >= 1000) THEN N'우수고객'
                     WHEN (@sum >= 1 ) THEN N'일반고객'
                     ELSE N'유령고객'
                END
  
                UPDATE userTbl SET grade = @userGrade WHERE userID = @id
     
                 FETCH NEXT FROM userCur INTO @id, @sum -- 다음행 값을 대입
            END

            CLOSE userCur  -- 커서 닫기
            DEALLOCATE userCur -- 커서 해제

CREATE PROC usp_case
     @userName nvarchar(10)
AS
 DECLARE @bYear INT
 DECLARE @tti NCHAR(3) -- 띠
 SELECT @bYear = birthYear FROM userTbl
  WHERE name = @userName;

 SET @tti =

PRINT @userName + '의 띠==> ' + @tti;

CREATE PROCEDURE   USP_IFELSE
      @userName  NVARCHAR(10)
AS
        DECLARE @bYear   INT
        SELECT @bYear = birthyear FROM USETBL
          WHERE NAME = @useName;