ALTER PROCEDURE [dbo].[uspInventoryBillSheet_GetAll]
@billType NVARCHAR(1000),
-- @stime NVARCHAR(1000), --开始时间
-- @etime NVARCHAR(1000), --结束时间
-- @SearchName varchar(1000),--商品信息
-- @Supplier varchar(1000),
-- @billsheetno varchar(1000),
-- @classnumber varchar(1000),
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@PageCount int out
AS
declare @p_count int,
@startPage int,
@endPage int
begin
DECLARE @sql NVARCHAR(4000)
SELECT @PageCount=count(*) FROM
[Inventory_BillSheet]
WHERE
BillType = @billType
set @startPage=(@PageIndex-1)*@PageSize+1
set @endPage =@PageIndex*@PageSize
select * from
(
SELECT distinct ROW_NUMBER() OVER (ORDER BY [Inventory_BillSheet].CreatedOn desc) AS pos, [Inventory_BillSheet].BillSheetId,
[Inventory_BillSheet].BillSheetNo,
[Inventory_BillSheet].BillType,
[Inventory_BillSheet].ConsignorId,
[Inventory_BillSheet].OrderStatusId,
[Inventory_BillSheet].Fees,
[Inventory_BillSheet].PayMoney,
[Inventory_BillSheet].Note,
[user_class].classnumber AS classnumber,
[Inventory_BillSheet].userclassid,
[Inventory_BillSheet].CreatedOn,
[Inventory_Consignor].[Name] AS ConsignorName,
(select distinct Count(*) from [Inventory_BillSheet_Item] where [Inventory_BillSheet_Item].BillSheetId = [Inventory_BillSheet].BillSheetId) as RecordCount,
(select sum(buyinprice*quantity) from [Inventory_BillSheet_Item] where [Inventory_BillSheet_Item].BillSheetId = [Inventory_BillSheet].BillSheetId) as RecordSum
FROM
[Inventory_BillSheet]
LEFT JOIN [Inventory_Consignor]
ON [Inventory_BillSheet].ConsignorId = [Inventory_Consignor].ConsignorId
LEFT JOIN [user_class]
ON [Inventory_BillSheet].userclassid=user_class.classid
LEFT JOIN [Inventory_BillSheet_Item]
ON [Inventory_BillSheet_Item].BillSheetId=[Inventory_BillSheet].BillSheetId
LEFT JOIN product
ON product.productid=[Inventory_BillSheet_Item].productid
WHERE
BillType =@billType and 怎样在这里加上上面注释的的条件
)as sp
where pos between @startPage and @endPage
end
@billType NVARCHAR(1000),
-- @stime NVARCHAR(1000), --开始时间
-- @etime NVARCHAR(1000), --结束时间
-- @SearchName varchar(1000),--商品信息
-- @Supplier varchar(1000),
-- @billsheetno varchar(1000),
-- @classnumber varchar(1000),
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@PageCount int out
AS
declare @p_count int,
@startPage int,
@endPage int
begin
DECLARE @sql NVARCHAR(4000)
SELECT @PageCount=count(*) FROM
[Inventory_BillSheet]
WHERE
BillType = @billType
set @startPage=(@PageIndex-1)*@PageSize+1
set @endPage =@PageIndex*@PageSize
select * from
(
SELECT distinct ROW_NUMBER() OVER (ORDER BY [Inventory_BillSheet].CreatedOn desc) AS pos, [Inventory_BillSheet].BillSheetId,
[Inventory_BillSheet].BillSheetNo,
[Inventory_BillSheet].BillType,
[Inventory_BillSheet].ConsignorId,
[Inventory_BillSheet].OrderStatusId,
[Inventory_BillSheet].Fees,
[Inventory_BillSheet].PayMoney,
[Inventory_BillSheet].Note,
[user_class].classnumber AS classnumber,
[Inventory_BillSheet].userclassid,
[Inventory_BillSheet].CreatedOn,
[Inventory_Consignor].[Name] AS ConsignorName,
(select distinct Count(*) from [Inventory_BillSheet_Item] where [Inventory_BillSheet_Item].BillSheetId = [Inventory_BillSheet].BillSheetId) as RecordCount,
(select sum(buyinprice*quantity) from [Inventory_BillSheet_Item] where [Inventory_BillSheet_Item].BillSheetId = [Inventory_BillSheet].BillSheetId) as RecordSum
FROM
[Inventory_BillSheet]
LEFT JOIN [Inventory_Consignor]
ON [Inventory_BillSheet].ConsignorId = [Inventory_Consignor].ConsignorId
LEFT JOIN [user_class]
ON [Inventory_BillSheet].userclassid=user_class.classid
LEFT JOIN [Inventory_BillSheet_Item]
ON [Inventory_BillSheet_Item].BillSheetId=[Inventory_BillSheet].BillSheetId
LEFT JOIN product
ON product.productid=[Inventory_BillSheet_Item].productid
WHERE
BillType =@billType and 怎样在这里加上上面注释的的条件
)as sp
where pos between @startPage and @endPage
end
BillType = @billType and 字段A=@stime and 字段B= @etime and ...
另外,你注释掉了就不能用了啊!
and ( CreatedOn<=@etime or isnull(@etime,'')='' )
and ( [Name]=@SearchName or isnull(@SearchName,'')='')
and ...
ALTER PROCEDURE [dbo].[uspInventoryBillSheet_GetAll]
@billType NVARCHAR(1000),
-- @stime NVARCHAR(1000), --开始时间
-- @etime NVARCHAR(1000), --结束时间
-- @SearchName varchar(1000),--商品信息
-- @Supplier varchar(1000),
-- @billsheetno varchar(1000),
-- @classnumber varchar(1000),
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@PageCount int out
AS
declare @p_count int,
@startPage int,
@endPage int
begin
DECLARE @sql NVARCHAR(4000)
SELECT @PageCount=count(*) FROM
[Inventory_BillSheet]WHERE
BillType = @billType set @startPage=(@PageIndex-1)*@PageSize+1
set @endPage =@PageIndex*@PageSizeDECLARE @s VARCHAR(8000)
SET @s='
select * from
(
SELECT distinct ROW_NUMBER() OVER (ORDER BY [Inventory_BillSheet].CreatedOn desc) AS pos,[Inventory_BillSheet].BillSheetId,
[Inventory_BillSheet].BillSheetNo,
[Inventory_BillSheet].BillType,
[Inventory_BillSheet].ConsignorId,
[Inventory_BillSheet].OrderStatusId,
[Inventory_BillSheet].Fees,
[Inventory_BillSheet].PayMoney,
[Inventory_BillSheet].Note,
[user_class].classnumber AS classnumber,
[Inventory_BillSheet].userclassid,
[Inventory_BillSheet].CreatedOn,
[Inventory_Consignor].[Name] AS ConsignorName,
(select distinct Count(*) from [Inventory_BillSheet_Item] where [Inventory_BillSheet_Item].BillSheetId = [Inventory_BillSheet].BillSheetId) as RecordCount,
(select sum(buyinprice*quantity) from [Inventory_BillSheet_Item] where [Inventory_BillSheet_Item].BillSheetId = [Inventory_BillSheet].BillSheetId) as RecordSum
FROM
[Inventory_BillSheet]
LEFT JOIN [Inventory_Consignor]
ON [Inventory_BillSheet].ConsignorId = [Inventory_Consignor].ConsignorId
LEFT JOIN [user_class]
ON [Inventory_BillSheet].userclassid=user_class.classid
LEFT JOIN [Inventory_BillSheet_Item]
ON [Inventory_BillSheet_Item].BillSheetId=[Inventory_BillSheet].BillSheetId
LEFT JOIN product
ON product.productid=[Inventory_BillSheet_Item].productid
WHERE
BillType ='+@billType+' AND 你的时间字段 BETWEEN '''+isnull(@stime,'1900-01-01')+''' AND '''+isnull(@etime,'9998-12-31')+''''+isnull(' AND 你的对应字段='''@SearchName+'''','')+
')as sp
where pos between '+rtrim(@startPage)+' and '+rtrim(@endPage)
EXEC (@s)
end