Код процедури

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
USE [mar]
GO
/****** Object: StoredProcedure [dbo].[task] Script Date: 20.04.2015 3:03:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[task]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE season CURSOR LOCAL
FOR SELECT ID, Назва FROM Сезони;
DECLARE @s_id INT;
DECLARE @colors VARCHAR(MAX);
DECLARE @names VARCHAR(MAX);
DECLARE @tcolor VARCHAR(50);
DECLARE @tname VARCHAR(50);
DECLARE @season_name VARCHAR(50);
DECLARE @result TABLE
(
Сезон varchar(50),
Тип varchar(MAX),
Колір varchar(MAX)
);
set @colors = ' ';
set @names = ' ';
OPEN season
FETCH NEXT FROM season INTO @s_id, @season_name;
WHILE @@FETCH_STATUS = 0
BEGIN
--вибірка кольорів
DECLARE colors_cur CURSOR LOCAL FOR
select одяг.колір
from Поєднання
join Одяг on Одяг.id=Поєднання.ОдягID
join Сезони on Сезони.id=Поєднання.СезонID
where Сезони.ID = @s_id
GROUP by Колір;
OPEN colors_cur;
FETCH NEXT FROM colors_cur INTO @tcolor;
WHILE @@FETCH_STATUS = 0
BEGIN
set @colors = @colors + @tcolor + ', ';
FETCH NEXT FROM colors_cur INTO @tcolor;
END
CLOSE colors_cur;
DEALLOCATE colors_cur;
--вибірка одягу
DECLARE names_cur CURSOR LOCAL FOR
select Одяг.Назва
from Поєднання
join Одяг on Одяг.id=Поєднання.ОдягID
join Сезони on Сезони.id=Поєднання.СезонID
where Сезони.ID = @s_id
GROUP by Одяг.Назва;
OPEN names_cur;
FETCH NEXT FROM names_cur INTO @tname;
WHILE @@FETCH_STATUS = 0
BEGIN
set @names = @names + @tname + ', ';
FETCH NEXT FROM names_cur INTO @tname;
END
CLOSE names_cur;
DEALLOCATE names_cur;
INSERT INTO @result(Сезон, Тип, Колір) VALUES (@season_name, @names, @colors);
--PRINT @colors;
set @colors = ' ';
--PRINT @names;
set @names = ' ';
FETCH NEXT FROM season INTO @s_id, @season_name;
END
CLOSE season;
DEALLOCATE season;
SELECT * FROM @result;
END