存储过程示例

Create proc CreateOrder
@orderId nvarchar(50),--订单号
@userId int,--用户编号
@address nvarchar(255),--收货人地址
@totalMoney money output --总金额
as
begin
  declare @error int
    set @error=0
--计算总金额
 begin transaction
 select @totalMoney=sum(UnitPrice*[Count]) from Books inner join Cart on Books.Id=Cart.BookId where Cart.UserId=@userId
 set @error=@@error+@error
--向订单主表中插入数据.
insert into dbo.Orders(OrderId, OrderDate, UserId, TotalPrice, PostAddress, state) values(@orderId,getdate(),@userId,@totalMoney,@address,0)
 set @error=@@error+@error
--向明细表中插入数据
 insert into dbo.OrderBook(OrderID, BookID, Quantity, UnitPrice)select @orderId,BookId,[Count],UnitPrice from Books inner join Cart on Books.Id=Cart.BookId where Cart.UserId=@userId
 set @error=@@error+@error
--删除购物车表中当前用户的数据
  delete from Cart where UserId=@userId
   set @error=@@error+@error
  if @error>0 
   begin
       rollback transaction
    end
  else
   begin
        commit transaction
   end
end