表里无此字段,但是存储过程得新增一条字段,DepartmentName

USE [DrugSaleSystem]
GO
/****** Object:  StoredProcedure [dbo].[ShiftStatistics_Get]    Script Date: 11/23/2016 16:38:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-----------------------------------------------------------------
-- Name: ShiftStatistics Store Procedure is to add a operation.
-- Author: 浪剑
-- DateTime: 2016-05-09 14:35:08
-- Description: 分页记录
-----------------------------------------------------------------
/*
@PageIndex 当前页码
@PageSize 每页的记录数
*/	
ALTER PROCEDURE [dbo].[ShiftStatistics_Get]	
(	
	@ShiftStatisticsCode varchar(20),
	@DepartmentCode int,
	@DepartmentName varchar(50)='',
	@IsSend int,
	@StartShiftDate varchar(20),
	@EndShiftDate varchar(20),
	@PageIndex int,
	@PageSize int
)	
AS	
BEGIN	
	DECLARE @Page int
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int
	SET @Page = (@PageIndex - 1)
	-- First set the rowcount
	SET @RowsToReturn = @PageSize * (@Page + 1)
	SET ROWCOUNT @RowsToReturn
	-- Set the page bounds
	SET @PageLowerBound = @PageSize * @Page
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1
	-- Create a temp table to store the select results
	CREATE TABLE #PageIndex 
	(
		IndexId int IDENTITY (1, 1) NOT NULL,
		ShiftStatisticsID int
	)
	DECLARE @STRSQL varchar(3000)
	DECLARE @CONSQL varchar(2000)
	SET @STRSQL='
	INSERT INTO #PageIndex (ShiftStatisticsID)
	SELECT 
		[ShiftStatisticsID]
	FROM 
		[ShiftStatistics] WHERE 8=8'
	SET @CONSQL=''
	IF(@ShiftStatisticsCode<>'')
	BEGIN
		SET @CONSQL=@CONSQL+' AND ShiftStatisticsCode like ''%' + @ShiftStatisticsCode + '%'''		
	END
	IF(@DepartmentCode>0)
	BEGIN
		SET @CONSQL=@CONSQL+' AND DepartmentCode=' + CAST(@DepartmentCode AS VARCHAR(20))		
	END
	IF(@DepartmentName<>'')
	BEGIN
		SET @CONSQL=@CONSQL+' AND DepartmentCode in(select DepartmentCode from Department where DepartmentName like ''%' + @DepartmentName + '%'')'	
	END
	IF(@IsSend>-1)
	BEGIN
		SET @CONSQL=@CONSQL+' AND IsSend=' + CAST(@IsSend AS VARCHAR(20))		
	END
	IF(@StartShiftDate<>'')
	BEGIN
		SET @CONSQL=@CONSQL+' AND ShiftDate>=''' + @StartShiftDate + ''''		
	END
	IF(@EndShiftDate<>'')
	BEGIN
		SET @CONSQL=@CONSQL+' AND ShiftDate<=''' + @EndShiftDate + ''''		
	END
	EXEC(@STRSQL+@CONSQL + ' order by ShiftDate desc')
	SELECT
		c.ShiftStatisticsID,
		ShiftStatisticsCode,
		DepartmentCode,
		POSID,
		CashierCode,
		ShiftDate,
		TotalSalesAmount,
		CashAmount,
		SocialInsuranceAmount,
		UnionpayAmount,
		ValueCardAmount,
		WeChatAmount,
		AlipayAmount,
		CouponAmount,
		IsSend,
		AmountCheckCode,
		Operator,
		OperateDate,
		Remark
	FROM [ShiftStatistics] c , #PageIndex PageIndex
	WHERE
	c.ShiftStatisticsID = PageIndex.ShiftStatisticsID AND
	PageIndex.IndexID > @PageLowerBound AND
	PageIndex.IndexID < @PageUpperBound
	order by ShiftDate desc
	EXEC('SELECT COUNT(ShiftStatisticsID) AS TotalRecords FROM [ShiftStatistics] where 8=8'+@CONSQL)
END	




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值