/* SQL scripts for daprototype */
Use northwind
go
/* Used in daprototype */
Create procedure spselcustomers
(
@ customerid varchar (5)
)
as
select
C. customerid, C. companyName, C. address, C. city
from MERs C
where c. customerid like @ customerid + '%'
go
/* Used in daprototype */
Create procedure spselorders
(
@ Customerid nchar (5)
)
As
Select O. orderid, O. customerid, O. orderdate
From
Orders o
Where o. customerid = @ customerid
Order by O. orderdate DESC
Go
/* Used in daprototype */
Create procedure spselorderdetail
(
@ Orderid int
)
As
Select OD. productid, OD. unitprice, OD. quantity, P. productname
From [Order Details] OD inner join products P
On OD. productid = P. productid
Where OD. orderid = @ orderid
Go
/* Used in daprototype */
Create procedure spuporderdetail
(
@ Orderid int,
@ Productid int,
@ Quantity int
)
As
Update [Order Details]
Set quantity = @ quantity
Where productid = @ productid and orderid = @ orderid
Go
/* Used in daprototype */
Create procedure spdelorders
(
@ Orderid int
)
As
SET transaction isolation level serializable
Begin transaction
Delete [Order Details] Where orderid = @ orderid
Delete orders where orderid = @ orderid
If @ error> 0
Rollback transaction
Else
Commit transaction
Go
/* Used in daprototype */
Create procedure spdelordersdemo
(
@ Orderid int
)
As
Delete orders where orderid = @ orderid
Go
/* Used in daprototype */
Create procedure spdeloddemo
(
@ Orderid int
)
As
Delete [Order Details] Where orderid = @ orderid
Go