Creating SQL-Customized functions and commodity paging SQL stored procedures

--Determine if the brand ID exists when the product is screened

--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

--to determine whether the price of the product filter input has
--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

--function equivalent to split
Create function F_split (@SourceSql varchar, @StrSeprate varchar)
Returns @temp table ( A varchar)
as
begin
Declare @i int
Set @SourceSql = RTRIM (LTRIM (@SourceSql))
Set @i = CHARINDEX (@ Strseprate, @SourceSql)--charindex returns the starting position of the specified expression in the string.
While @i >= 1
Begin
Insert @temp values (left (@SourceSql, @i-1)
Set @SourceSql =substring (@SourceSql, @ I+1,len (@SourceSql) [email protected])
Set @i=charindex (@StrSeprate, @SourceSql)
End
If @SourceSql <> '
Insert @temp values (@SourceSql)
return
End
Go
--select * from Dbo.f_split (' 198,199,204,210 ', ', ')

--function to determine whether the SKU contains the filter attribute value string for this input
Create function isexists (@stringCode varchar ($), @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 by Dbo.f_split (@stringCode, ', ')) J INNER JOIN (select a from Dbo.f_split (@numb ERs, ', ')) 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

----Product Page Refresh Filter
Create proc Getextractcommodityrefresh
(
@SortId int,--Commodity category ID
@BrandId int,--Brand ID
@PriceA decimal (8,2),--query Price A
@PriceB decimal (8,2),--query price b
@PageNumber int,--Pages
@CommoditiesPerPage int,
@StrSelectionValueId varchar,--Commodity Filter Property value ID
Sort @orderAse int,--sales price score Shelf Time @orderAse = 3 for descending (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 SKUs s on C.commodityid = S.Comm Odityid) as CC
where SortId = @SortId and 1 = (select Dbo.isvalite (Brandid, @BrandId)) and 1= (select Dbo.isexists (Cselectionattribute, @St Rselectionvalueid) 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 SKUs s on C.commodityid = S.Comm Odityid) as CC
where SortId = @SortId and 1 = (select Dbo.isvalite (Brandid, @BrandId)) and 1= (select Dbo.isexists (Cselectionattribute, @St Rselectionvalueid) 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 SKUs s on C.commodityid = S.Comm Odityid) as CC
where SortId = @SortId and 1 = (select Dbo.isvalite (Brandid, @BrandId)) and 1= (select Dbo.isexists (Cselectionattribute, @St Rselectionvalueid) 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 SKUs s on C.commodityid = S.Com Modityid) as CC
where SortId = @SortId and 1 = (select Dbo.isvalite (Brandid, @BrandId)) and 1= (select Dbo.isexists (Cselectionattribute, @St Rselectionvalueid) 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 SKUs s on C.commodityid = S.Com Modityid) as CC
where SortId = @SortId and 1 = (select Dbo.isvalite (Brandid, @BrandId)) and 1= (select Dbo.isexists (Cselectionattribute, @St Rselectionvalueid) 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

----Extract product condition by product selection criteria
Create proc Getselectionconditioncommodities
(
@SortId int,
@BrandId int,
@PriceA Decimal (8,2),
@PriceB Decimal (8,2),
@StrSelectionValueId varchar,--Commodity Filter Property value 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 SKUs s on C.commodityid = S.Comm Odityid) as CC
where SortId = @SortId and 1 = (select Dbo.isvalite (Brandid, @BrandId)) and 1= (select Dbo.isexists (Cselectionattribute, @St Rselectionvalueid) 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