CREATE FUNCTION fnSelectBirthdayByMonth ()
RETURNS @retCalendar TABLE
( BirthMonth INT PRIMARY KEY,
Employees NVARCHAR(100) )
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
DECLARE @Calendar TABLE
( BirthMonth INT PRIMARY KEY,
Employees NVARCHAR(100) )
--(2)初期化処理
SET @Counter = 1
WHILE @Counter < 13
BEGIN
INSERT INTO @Calendar ( BirthMonth, Employees )
VALUES ( @Counter, '' )
SET @Counter = @Counter + 1
END
--(3)メイン処理
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
INSERT INTO @retCalendar
SELECT BirthMonth, Employees FROM @Calendar
--(4)終了処理
CLOSE EmployeeBirthday
DEALLOCATE EmployeeBirthday
RETURN
END |
|