代码改变世界

存储过程 （模糊查询/分页存储查询）

2018-11-08 19:33  King_Kai  阅读(415)  评论(0)    收藏  举报 
 1 dbcc checkident('Skus',reseed,0) 重制表数据    alter 更改
 2 
 3 --更改类别
 4 create proc UpdateCategory
 5 @categoryId int,
 6 @categoryName varchar(100)
 7 as
 8 update Categories set CategoryName = @categoryName where CategoryId=@categoryId
 9 
10 --添加商品
11 create proc AddGoodsSkuImg
12 (
13 @GoodsName varchar(300), 
14 @CategoryId int, 
15 @SkuString varchar(200),
16 @SkuPrice money,
17 @SkuInStock int,
18 @ImgName varchar(300)
19 )
20 as
21 insert into Goods values(@GoodsName,@CategoryId,null)
22 declare @goodsId int,@skuId int;
23 select @goodsId= ident_current('Goods')
24 insert into Skus values (@SkuString,@SkuPrice,@SkuInStock,@goodsId)
25 select @skuId =@@IDENTITY
26 insert into Albums values(@ImgName,null,@skuId,null,0,0,0,null)
27 go
28 
29 --模糊查询商品
30 create proc mfcxQueryGoods
31 @good varchar(100)
32 as
33 select Skus.SkuId, Categories.CategoryName, Goods.GoodsName, Skus.SkuPrice, Albums.PictureUrl from Goods, Categories, Skus, Albums
34 where Categories.CategoryId = Goods.CategoryId and Goods.GoodsId = Skus.GoodsId and Skus.SkuId = Albums.SkuId
35 and PictureId in(select Min(PictureId) from Albums group by Albums.SkuId)
36 and (Goods.GoodsId like '%'+@good+'%' or GoodsName like '%'+@good+'%' or CategoryName like '%'+@good+'%')
37 go
38 
39 --分页存储查询订单
40 create proc GetOrdersInfoByPage
41 (
42 @PageNumber int,  --页号
43 @OrdersPerPage int,  --每页显示记录数
44 @OrdersTotal int output  --总页数
45 )
46 as
47 declare @OrdersInfo table
48 (
49     RowNumber int,    --行号
50     SkuOrdersDetailsId int,  --销售记录编号
51     GoodsName varchar(100),  --商品名称
52     UserName varchar(100),  --用户名称
53     OrderDate DateTime,     --订单时间
54     ReceiverName varchar(100),  --收货人名称
55     ReceiverPhone varchar(15) --收货人电话
56 )
57 insert into @OrdersInfo
58 select ROW_NUMBER() over (order by SkuOrdersDetailsId), SkuOrdersDetailsId, Goods.GoodsName, Users.UserName, Orders.OrderDate, ReceiverName, ReceiverPhone
59 from SkuOrdersDetails, ReceivingInfoes, Goods, Users, Orders, Skus
60 where Goods.GoodsId = Skus.GoodsId and Skus.SkuId = SkuOrdersDetails.SkuId and SkuOrdersDetails.OrderId = Orders.OrderId
61 and Orders.UserId = Users.UserId and Users.UserId = ReceivingInfoes.UserId
62 select @OrdersTotal=COUNT(SkuOrdersDetailsId) from @OrdersInfo
63 select * from @OrdersInfo
64 where RowNumber>(@PageNumber-1)*@OrdersPerPage and RowNumber<=@PageNumber*@OrdersPerPage
65 go
66 
67 --创建供应商表
68 create table Supplier
69 (
70     supplierId char(5) primary key,
71     supplierName varchar(50) not null,
72     supplierTel char(11),
73     supplierVisible bit default 1
74 )
75 go
76 --删除 
77 create procedure DeleteSupplier
78     @supplierId char(5)
79 as
80 update Supplier set supplierVisible = 0 where supplierId = @supplierId
81 go