CREATE PROCEDURE SelectBirthdayByMonth AS
BEGIN
--(1)変数とカーソルの宣言
DECLARE @EmployeeName NVARCHAR(31)
DECLARE @BirthDate DATETIME
DECLARE @Counter INT
DECLARE EmployeeBirthday CURSOR FOR
SELECT FirstName + LastName As EmployeeName, BirthDate
FROM Employees
--(2)一時テーブルの作成
CREATE TABLE #Calendar
( BirthMonth INT PRIMARY KEY,
Employees NVARCHAR(100) )
--(3)初期化処理
SET NOCOUNT ON
SET @Counter = 1
WHILE @Counter < 13
BEGIN
INSERT INTO #Calendar ( BirthMonth, Employees )
VALUES ( @Counter, '' )
SET @Counter = @Counter + 1
END
--(4)メイン処理
OPEN EmployeeBirthday
FETCH NEXT FROM EmployeeBirthday
INTO @EmployeeName, @BirthDate
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Calendar
SET Employees = Employees + @EmployeeName
+ '(' + CAST( DAY(@BirthDate) AS NVARCHAR(2) ) + ') '
WHERE BirthMonth = MONTH(@BirthDate)
FETCH NEXT FROM EmployeeBirthday
INTO @EmployeeName, @BirthDate
END
SELECT * FROM #Calendar
--(5)終了処理
CLOSE EmployeeBirthday
DEALLOCATE EmployeeBirthday
END |
|