T-SQL-определения хранимых процедур

Вставка или обновление данных в Users:
CREATE PROCEDURE [dbo].[InsertOrUpdDataInUsers] @login varchar(40), @pass varchar(40), @role bit, @empID int, @id int=0, @mode varchar(30)
AS SET NOCOUNT ON
if(@mode='insert') begin
INSERT INTO [dbo].[Users] ([login] ,[pass] ,[role] ,[emp_id]) VALUES
(@login ,@pass ,@role ,@empID) end
if(@mode='update') begin
UPDATE Users SET [login] = @login ,[pass] = @pass ,[role] = @role
,[emp_id] = @empID WHERE Users.id=@idend
Вставка или обновление данных в Departments:
CREATE PROCEDURE InsertOrUpdDataInDepartments
@title varchar(255), @desc text, @mode varchar(30), @id int=0
AS BEGIN
SET NOCOUNT ON;
if(@mode='insert') begin
INSERT INTO [dbo].[Departments]
([title] ,[description])
VALUES(@title ,@desc)
end
if(@mode='update')begin
UPDATE [dbo].[Departments]
SET [title] = @title ,[description] = @desc WHERE Departments.id=@id
End END GO
Добавление/обновление данных в Employees:
CREATE PROCEDURE [dbo].[InsertOrUpdDataInEmployees] @name varchar(80), @lastname varchar(80),
@patronymic varchar(80), @mail varchar(80), @phone varchar(20), @depid int, @sid int, @house int, @flat int, @place varchar(255), @id int=0, @mode varchar(30)
AS
SET NOCOUNT ON
if(@mode='insert') begin
INSERT INTO [dbo].[Employees] ([name] ,[mail] ,[phone] ,[dep_id]
,[lastname] ,[patronymic] ,[s_id] ,[house] ,[flat] ,[place])
VALUES
(@name, @lastname, @patronymic, @mail, @phone, @depid, @sid, @house, @flat, @place)
end
if(@mode='update') begin
UPDATE [dbo].[Employees]
SET [name] = @name ,[mail] = @mail ,[phone] = @phone
,[dep_id] = @depid ,[lastname] = @lastname ,[patronymic] = @patronymic
,[s_id] = @sid ,[house] = @house ,[flat] = @flat ,[place] = @place
WHERE Employees.id=@id
end
Добавление/обновление данных в Orders:
CREATE PROCEDURE [dbo].[InsertOrUpdDataInOrders]
@create_date datetime, @end_date datetime,
@total real, @comment text, @c_id int,
@e_id int, @house int, @s_id int, @flat int,
@d_id int, @id int=0, @mode varchar(30)
AS SET NOCOUNT ON
if(@mode='insert') begin
INSERT INTO [dbo].[Orders]
([create_date] ,[end_date] ,[total] ,[comment] ,[c_id] ,[e_id] ,[house]
,[s_id] ,[flat] ,[d_id])
VALUES
(@create_date, @end_date, @total, @comment, @c_id, @e_id, @house, @s_id, @flat, @d_id)
end
if(@mode='update') begin
UPDATE [dbo].[Orders]
SET [create_date] = @create_date ,[end_date] = @end_date ,[total] = @total
,[comment] = @comment ,[c_id] = @c_id ,[e_id] = @e_id ,[house] = @house
,[s_id] = @s_id ,[flat] = @flat ,[d_id] = @d_id WHERE Orders.id=@id
end
Вставка/обновление для Streets:
CREATE PROCEDURE InsertOrUpdDataInStreets
@title varchar(255), @mode varchar(30), @id int=0 AS
BEGIN
SET NOCOUNT ON;
if(@mode='insert') begin
INSERT INTO [dbo].[Streets] ([title] )
VALUES(@title ) end
if(@mode='update') begin
UPDATE [dbo].[Departments] SET [title] = @title WHERE Departments.id=@id end END GO
Вставка/обновление для Clients:
CREATE PROCEDURE [dbo].[InsertOrUpdDataInClients] @name varchar(80), @lastname varchar(80),
@patronymic varchar(80), @mail varchar(80), @phone varchar(20), @id int=0, @mode varchar(30) AS
SET NOCOUNT ON
if(@mode='insert') begin
INSERT INTO [dbo].[Clients] ([lastname] ,[phone] ,[mail] ,[name]
,[patronymic]) VALUES (@name, @lastname, @patronymic, @mail, @phone) end
if(@mode='update') begin
UPDATE [dbo].[Clients]
SET [lastname] = @lastname ,[phone] = @phone ,[mail] = @mail
,[name] = @name ,[patronymic] = @patronymic
WHERE Clients.id=@id
end
Добавление или обновление для Deliveries:
CREATE PROCEDURE InsertOrUpdDataInDeliveries
@title varchar(255), @mode varchar(30), @id int=0
AS BEGIN
SET NOCOUNT ON;
if(@mode='insert') begin
INSERT INTO [dbo].[Deliveries] ([title])
VALUES(@title ) end
if(@mode='update') begin
UPDATE [dbo].[Deliveries] SET [title] = @title WHERE Deliveries.id=@id
End END
Добавление или обновление для Deliveries:
CREATE PROCEDURE InsertOrUpdDataInDeliveries
@title varchar(255), @mode varchar(30), @id int=0 AS BEGIN
SET NOCOUNT ON;
if(@mode='insert') begin
INSERT INTO [dbo].[Deliveries] ([title]) VALUES(@title)
end
if(@mode='update') begin
UPDATE [dbo].[Deliveries] SET [title] = @title WHERE Deliveries.id=@id
End END
Добавление/обновление данных в Themes:
CREATE PROCEDURE InsertOrUpdDataInThemes
@title varchar(255), @mode varchar(30), @id int=0 AS
BEGIN SET NOCOUNT ON;
if(@mode='insert') begin
INSERT INTO [dbo].[Themes] ([title]) VALUES(@title)
end
if(@mode='update') begin
UPDATE [dbo].[Themes] SET [title] = @title WHERE Themes.id=@id
End END
Добавление/обновление данных в Journals:
CREATE PROCEDURE [dbo].[InsertOrUpdDataInJournals]
@title varchar(255), @year int, @number varchar(20),@price int,
@themeID int, @pubid int, @id int=0, @mode varchar(30)
AS SET NOCOUNT ON
if(@mode='insert') begin
INSERT INTO [dbo].[Journals] ([title] ,[year] ,[number] ,[price] ,[theme_id]
,[pub_id]) VALUES (@title, @year , @number ,@price ,@themeID ,@pubid) end
if(@mode='update') begin
UPDATE [dbo].[Journals] SET [title] = @title ,[year] = @year
,[number] = @number ,[price] = @price ,[theme_id] = @themeID
,[pub_id] = @pubid WHERE Journals.inv_id=@id end
Добавление/обновление данных в Supply:
CREATE PROCEDURE [dbo].[InsertOrUpdDataInSupply]
@delivery_date datetime, @p_id int, @id int=0, @mode varchar(30)
AS SET NOCOUNT ON
if(@mode='insert') begin
INSERT INTO [dbo].[Supply] ([delivery_date] ,[p_id])
VALUES (@delivery_date, @p_id) end
if(@mode='update') begin
UPDATE [dbo].[Supply] SET [delivery_date] = @delivery_date
,[p_id] = @p_id WHERE Supply.id=@id end
Вставка/обновление для Publishings:
CREATE PROCEDURE InsertOrUpdDataInPublishings
@title varchar(255), @phone varchar(20), @person varchar(300), @mode varchar(30), @id int=0 AS
BEGIN
SET NOCOUNT ON;
if(@mode='insert') begin
INSERT INTO [dbo].[Publishings] ([title] ,[phone] ,[person])
VALUES(@title ,@phone, @person) end
if(@mode='update') begin
UPDATE [dbo].[Publishings] SET [title] = @title ,[phone] = @phone
,[person] = @person WHERE Publishings.id=@id
End END
Вставка/обновление для Suppliers:
CREATE PROCEDURE InsertOrUpdDataInSuppliers
@title varchar(255), @phone varchar(20), @person varchar(300), @mode varchar(30), @id int=0 AS BEGIN
SET NOCOUNT ON;
if(@mode='insert') begin
INSERT INTO [dbo].[Suppliers] ([title] ,[phone] ,[person])
VALUES(@title ,@phone, @person)
end
if(@mode='update') begin
UPDATE [dbo].[Suppliers] SET [title] = @title ,[phone] = @phone
,[person] = @person WHERE Suppliers.id=@id
End END
Вставка/обновление в Supply:
CREATE PROCEDURE [dbo].[InsertOrUpdDataInSupply]
@delivery_date datetime, @p_id int,@id int=0, @mode varchar(30)
AS SET NOCOUNT ON
if(@mode='insert') begin
INSERT INTO [dbo].[Supply]
([delivery_date] ,[p_id]) VALUES (@delivery_date, @p_id)
end
if(@mode='update') begin
UPDATE [dbo].[Supply] SET [delivery_date] = @delivery_date
,[p_id] = @p_id WHERE Supply.id=@id end
Вычисление общей суммы для заявки:
CREATE PROCEDURE CountTotalForOrder
@oid int AS BEGIN
SET NOCOUNT ON;
select sum(price*amount) as 'total'
from Journals, JournalsOrder
where Journals.inv_id=JournalsOrder.j_id and JournalsOrder.o_id = @oid;
END
Вычисление общей суммы по заявкам клиента:
CREATE PROCEDURE CountTotalForClient
@cid int AS BEGIN
SET NOCOUNT ON;
select sum(total) as 'Общая стоимость заявок' from clients, orders where clients.id=orders.c_id and clients.id=@cid; END
Поиск средней стоимости заявки:
CREATE PROCEDURE FindAvgOrder AS
BEGIN SET NOCOUNT ON;
select AVG(total) as 'Средняя стоимость' from Orders; END
Поиск поставщика, не выполнившего ни одной поставки за полгода:
CREATE PROCEDURE FindNotActiveSupplier
AS BEGIN
SET NOCOUNT ON;
exec ('create view maxdates as select suppliers.title, max(delivery_date) as "Последняя доставка" from supply, suppliers where supply.p_id=suppliers.id group by suppliers.title');
select * from (
select suppliers.title, max(delivery_date) as 'Последняя доставка' from supply, suppliers where supply.p_id=suppliers.id group by suppliers.title
) as q
where datediff(month, q.[Последняя доставка], GETDATE()) 6;
drop view maxdates; END
Поиск наиболее часто встречающегося издательства:
CREATE PROCEDURE FindMostUsePublishing
AS BEGIN
SET NOCOUNT ON;
exec ('create view countpubs as select count(Publishings.id) as "pamount" , Publishings.title from Journals, JournalsOrder, Publishings where Journals.inv_id = JournalsOrder.j_id and Publishings.id= Journals.pub_id group by Publishings.id, Publishings.title;');
select pamount, title from countpubs
where pamount=(select max(pamount) from countpubs);
drop view countpubs; END
Поиск менеджера, оформившего наибольшее количество сделок:
CREATE PROCEDURE FindManagerWithMaxOrders
AS BEGIN SET NOCOUNT ON;
exec ('create view numOrdersForManagers_ as select count(orders.id) as "amount", employees.lastname, employees.name from employees, orders where orders.e_id=employees.id and place="Менеджер" group by employees.lastname, employees.name;');
select * from numOrdersForManagers_
where amount=(select max(amount) from numOrdersForManagers)
drop view numOrdersForManagers_; END