3. Описание скриптов для добавления данных

--Добавление единицы измерения

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_ed_izm') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_ed_izm

go

CREATE procedure Add_ed_izm

@ed_izm varchar(5),

@p_name_ed_izm varchar(50)

as

INSERT INTO Ed_izm(ed_izm,p_name_ed_izm)

VALUES (@ed_izm,@p_name_ed_izm)

go

--exec Add_ed_izm '1','12345'

--go

--select * from Ed_izm

--Добавление группы

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_group') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_group

go

CREATE procedure Add_group

@name_group varchar(25),

@description_currency varchar(1000)

as

INSERT INTO Product_groups(name_group,description_currency)

VALUES (@name_group,@description_currency)

go

--exec Add_group '1','12345'

--go

--select * from Product_groups

--Добавление товара в ассортимент

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_good') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_good

go

CREATE procedure Add_good

@name_good varchar(25),

@id_group int,

@ed_izm varchar(5),

@quantity_good int,

@p_cost_good smallmoney,

@e_cost_good smallmoney

as

INSERT INTO Goods(name_good,id_group,ed_izm,quantity_good,p_cost_good,e_cost_good)

VALUES (@name_good,@id_group,@ed_izm,@quantity_good,@p_cost_good,@e_cost_good)

go

--exec Add_good '3', 4, '1', 5, 6.00, 7.00

--go

--select * from Goods

--Добавление поставщика

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_supplier') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_supplier

go

CREATE procedure Add_supplier

@name_supplier varchar(25),

@address_supplier varchar(500),

@telephon_supplier varchar(500)

as

INSERT INTO Suppliers(name_supplier,address_supplier,telephon_supplier)

VALUES (@name_supplier,@address_supplier,@telephon_supplier)

go

--exec Add_supplier '1','2','3'

--go

--select * from Suppliers

--go

--Добавление поставки

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_delivery') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_delivery

go

CREATE procedure Add_delivery

@date_time_delivery datetime,

@id_supplier int

as

INSERT INTO Deliveries(date_time_delivery,id_supplier)

VALUES (@date_time_delivery,@id_supplier)

go

--exec Add_delivery '2010-05-07 03:05:56',3

--go

--select * from Deliveries

--Добавление накладной

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_cons_note') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_cons_note

go

CREATE procedure Add_cons_note

@id_cons_note int,

@id_good int,

@quantity int,

@cost smallmoney

as

INSERT INTO Consignment_note(id_cons_note,id_good,quantity,cost)

VALUES (@id_cons_note,@id_good,@quantity,@cost)

go

--exec Add_cons_note 5,52,3,100.00

--go

--select * from Consignment_note

--Добавление клиента

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_client') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_client

go

CREATE procedure Add_client

@discount_client int

as

INSERT INTO Clients(discount_client)

VALUES (@discount_client)

go

--exec Add_client 1

--go

--select * from Clients

--Добавление продажи

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_shipment') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_shipment

go

CREATE procedure Add_shipment

@date_time_shipment datetime,

@id_client int

as

INSERT INTO Shipments(date_time_shipment,id_client)

VALUES (@date_time_shipment,@id_client)

go

--exec Add_shipment '2011-05-03 13:45:56',7

--go

--select * from Shipments

--Добавление заказа

if exists (select * from dbo.sysobjects

where id = OBJECT_ID(N'Add_zakaz') and OBJECTPROPERTY(id, N'IsProcedure')=1

)

drop procedure Add_zakaz

go

CREATE procedure Add_zakaz

@id_zakaz int,

@id_good int,

@quantity int

as

INSERT INTO Zakaz(id_zakaz,id_good,quantity,cost)

VALUES (@id_zakaz,@id_good,@quantity,

(select G.e_cost_good

from Goods G

where G.id_good=@id_good)

)

go

--exec Add_zakaz 4,34,3

--go

--select * from Zakaz