创建sql自定义的函数及商品分页sql存储过程

待我称王封你为后i 2022-01-09 11:25 185阅读 0赞

\--商品筛选时判断品牌ID是否存在

\--select dbo.isValite(94,94)  
create function isValite(@brandId int,@bId int)  
returns int  
as  
begin  
Declare @rNumber int  
if @brandId = @bId  
set @rNumber = 1  
else  
set @rNumber = 0  
if @bId = 0  
set @rNumber = 1  
return @rNumber  
end  
go

\--判断商品筛选输入价格是否有  
\--select dbo.comparePrice(269.00,100,300)  
create function comparePrice(@price decimal(8,2),@priceA decimal(8,2), @priceB decimal(8,2))  
returns int  
as  
begin  
declare @j int  
if @price >= @priceA and @price <= @priceB  
set @j = 1  
else  
set @j = 0  
if @priceA = 0 and @priceB = 0  
set @j = 1  
return @j  
end  
go

\--函数相当于split  
create function f\_split(@SourceSql varchar(1000),@StrSeprate varchar(10))  
returns @temp table(a varchar(100))  
as  
begin  
declare @i int  
set @SourceSql = RTRIM(LTRIM(@SourceSql))  
set @i = charindex(@StrSeprate,@SourceSql)--CHARINDEX 返回字符串中指定表达式的起始位置。  
while @i >= 1  
begin  
insert @temp values(left(@SourceSql,@i-1))  
set @SourceSql=substring(@SourceSql,@i+1,LEN(@SourceSql)-@i)  
set @i=charindex(@StrSeprate,@SourceSql)  
end  
if @SourceSql<>'\\'  
insert @temp values(@SourceSql)  
return  
end  
go  
\-- select \* from dbo.f\_split('198,199,204,210',',')

\--函数判断sku是否包含该输入的筛选属性值串  
create function isExists(@stringCode varchar(500),@numbers varchar(500))  
returns int  
as  
begin  
declare @returnValue int  
if @numbers = '0'  
set @returnValue = 1  
else if (select count(a) from dbo.f\_split(@stringCode,',')) < (select count(a) from dbo.f\_split(@numbers,','))  
set @returnValue = 0  
else if (select count(j.a) from (select a from dbo.f\_split(@stringCode,',')) j inner join (select a from dbo.f\_split(@numbers,',')) s on j.a = s.a ) = (select count(a) from dbo.f\_split(@numbers,','))  
set @returnValue = 1  
else  
set @returnValue = 0  
return @returnValue  
end  
go

\----商品分页刷新筛选  
create proc GetExtractCommodityRefresh  
(  
@SortId int,--商品类别Id  
@BrandId int,--品牌ID  
@PriceA decimal(8,2),--查询价格A  
@PriceB decimal(8,2),--查询价格B  
@PageNumber int,--页数  
@CommoditiesPerPage int,  
@StrSelectionValueId varchar(500),--商品筛选属性值Id  
@orderAse int,-- 排序 销量 价格 评分 上架时间 @orderAse = 3 为降序(desc)  
@HowManyCommodities int output  
)  
as  
declare @Commodity table  
(  
RowNumber int,  
CommodityId int,  
CommodityName varChar(300),  
MarketPrice decimal(8,2),  
CommodityAddTime DateTime,  
BrandId int,  
SortId int,  
CSelectionAttribute varchar(300),  
SkuImg varchar(300),  
SkuPrice decimal(8,2),  
SkuQuanlity int  
)  
if @orderAse = 1  
insert into @Commodity  
select ROW\_NUMBER() over(order by cc.commodityId),cc.CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg, SkuPrice,SkuQuanlity from (Select distinct c.CommodityId,c.CommodityName,c.MarketPrice,c.CommodityAddTime,c.BrandId,c.SortId,c.CSelectionAttribute,s.SkuImg, s.SkuPrice,s.SkuQuanlity from Commodity c left join sku s on c.CommodityId = s.CommodityId ) as cc  
where SortId = @SortId and 1 = (select dbo.isValite(BrandId,@BrandId))and 1= (select dbo.isExists(CSelectionAttribute,@StrSelectionValueId))and 1 = (select dbo.comparePrice(SkuPrice,@PriceA,@PriceB))  
  
else if @orderAse = 2  
insert into @Commodity  
select ROW\_NUMBER() over(order by cc.SkuPrice),cc.CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg, SkuPrice,SkuQuanlity from (Select distinct c.CommodityId,c.CommodityName,c.MarketPrice,c.CommodityAddTime,c.BrandId,c.SortId,c.CSelectionAttribute,s.SkuImg, s.SkuPrice,s.SkuQuanlity from Commodity c left join sku s on c.CommodityId = s.CommodityId ) as cc  
where SortId = @SortId and 1 = (select dbo.isValite(BrandId,@BrandId))and 1= (select dbo.isExists(CSelectionAttribute,@StrSelectionValueId))and 1 = (select dbo.comparePrice(SkuPrice,@PriceA,@PriceB))  
else if @orderAse = 3  
insert into @Commodity  
select ROW\_NUMBER() over(order by cc.SkuPrice desc),cc.CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg, SkuPrice,SkuQuanlity from (Select distinct c.CommodityId,c.CommodityName,c.MarketPrice,c.CommodityAddTime,c.BrandId,c.SortId,c.CSelectionAttribute,s.SkuImg, s.SkuPrice,s.SkuQuanlity from Commodity c left join sku s on c.CommodityId = s.CommodityId ) as cc  
where SortId = @SortId and 1 = (select dbo.isValite(BrandId,@BrandId))and 1= (select dbo.isExists(CSelectionAttribute,@StrSelectionValueId))and 1 = (select dbo.comparePrice(SkuPrice,@PriceA,@PriceB))  
  
else if @orderAse = 4  
insert into @Commodity  
select ROW\_NUMBER() over(order by cc.commodityId desc),cc.CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg, SkuPrice,SkuQuanlity from (Select distinct c.CommodityId,c.CommodityName,c.MarketPrice,c.CommodityAddTime,c.BrandId,c.SortId,c.CSelectionAttribute,s.SkuImg, s.SkuPrice, s.SkuQuanlity from Commodity c left join sku s on c.CommodityId = s.CommodityId ) as cc  
where SortId = @SortId and 1 = (select dbo.isValite(BrandId,@BrandId))and 1= (select dbo.isExists(CSelectionAttribute,@StrSelectionValueId))and 1 = (select dbo.comparePrice(SkuPrice,@PriceA,@PriceB))  
  
else if @orderAse = 5  
insert into @Commodity  
select ROW\_NUMBER() over(order by cc.CommodityAddTime desc),cc.CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg, SkuPrice,SkuQuanlity from (Select distinct c.CommodityId,c.CommodityName,c.MarketPrice,c.CommodityAddTime,c.BrandId,c.SortId,c.CSelectionAttribute,s.SkuImg, s.SkuPrice ,s.SkuQuanlity from Commodity c left join sku s on c.CommodityId = s.CommodityId ) as cc  
where SortId = @SortId and 1 = (select dbo.isValite(BrandId,@BrandId))and 1= (select dbo.isExists(CSelectionAttribute,@StrSelectionValueId))and 1 = (select dbo.comparePrice(SkuPrice,@PriceA,@PriceB))  
  
select @HowManyCommodities = count(\*) from @Commodity  
select CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg,SkuPrice,SkuQuanlity from @Commodity where RowNumber > (@PageNumber - 1) \* @CommoditiesPerPage  
and RowNumber <= @PageNumber \* @CommoditiesPerPage  
go  
  
declare @HowManyCommodities real  
exec GetExtractCommodityRefresh 19,0,0,1000,1,20,'0',1,@HowManyCommodities output  
select @HowManyCommodities  
go

\----通过商品筛选条件提取商品condition  
create proc GetSelectionConditionCommodities  
(  
@SortId int,  
@BrandId int,  
@PriceA decimal(8,2),  
@PriceB decimal(8,2),  
@StrSelectionValueId varchar(500),--商品筛选属性值Id  
@HowManyCommodities int output  
)  
as  
declare @Commodity table  
(  
RowNumber int,  
CommodityId int,  
CommodityName varChar(300),  
MarketPrice decimal(8,2),  
CommodityAddTime DateTime,  
BrandId int,  
SortId int,  
CSelectionAttribute varchar(300),  
SkuImg varchar(300),  
SkuPrice decimal(8,2),  
SkuQuanlity int  
)  
insert into @Commodity  
select ROW\_NUMBER() over(order by cc.commodityId),cc.CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg, SkuPrice,SkuQuanlity from (Select distinct c.CommodityId,c.CommodityName,c.MarketPrice,c.CommodityAddTime,c.BrandId,c.SortId,c.CSelectionAttribute,s.SkuImg, s.SkuPrice,s.SkuQuanlity from Commodity c left join sku s on c.CommodityId = s.CommodityId ) as cc  
where SortId = @SortId and 1 = (select dbo.isValite(BrandId,@BrandId))and 1= (select dbo.isExists(CSelectionAttribute,@StrSelectionValueId))and 1 = (select dbo.comparePrice(SkuPrice,@PriceA,@PriceB))  
  
select @HowManyCommodities = count(\*) from @Commodity  
select CommodityId,CommodityName,MarketPrice,CommodityAddTime,BrandId,SortId,CSelectionAttribute,SkuImg,SkuPrice,SkuQuanlity from @Commodity  
  
go  
  
declare @HowManyCommodities real  
exec GetSelectionConditionCommodities 19,0,0,0,'0',@HowManyCommodities output  
select @HowManyCommodities  
go

转载于:https://www.cnblogs.com/simpleBlue3/p/3879491.html