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

来源：互联网发布：excel同列不同数据分列编辑：程序博客网时间：2024/06/04 18:27
USE [DrugSaleSystem]GO/****** Object:  StoredProcedure [dbo].[ShiftStatistics_Get]    Script Date: 11/23/2016 16:38:58 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO------------------------------------------------------------------- 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)ASBEGINDECLARE @Page intDECLARE @PageLowerBound intDECLARE @PageUpperBound intDECLARE @RowsToReturn intSET @Page = (@PageIndex - 1)-- First set the rowcountSET @RowsToReturn = @PageSize * (@Page + 1)SET ROWCOUNT @RowsToReturn-- Set the page boundsSET @PageLowerBound = @PageSize * @PageSET @PageUpperBound = @PageLowerBound + @PageSize + 1-- Create a temp table to store the select resultsCREATE 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<>'')BEGINSET @CONSQL=@CONSQL+' AND ShiftStatisticsCode like ''%' + @ShiftStatisticsCode + '%'''ENDIF(@DepartmentCode>0)BEGINSET @CONSQL=@CONSQL+' AND DepartmentCode=' + CAST(@DepartmentCode AS VARCHAR(20))ENDIF(@DepartmentName<>'')BEGINSET @CONSQL=@CONSQL+' AND DepartmentCode in(select DepartmentCode from Department where DepartmentName like ''%' + @DepartmentName + '%'')'ENDIF(@IsSend>-1)BEGINSET @CONSQL=@CONSQL+' AND IsSend=' + CAST(@IsSend AS VARCHAR(20))ENDIF(@StartShiftDate<>'')BEGINSET @CONSQL=@CONSQL+' AND ShiftDate>=''' + @StartShiftDate + ''''ENDIF(@EndShiftDate<>'')BEGINSET @CONSQL=@CONSQL+' AND ShiftDate<=''' + @EndShiftDate + ''''ENDEXEC(@STRSQL+@CONSQL + ' order by ShiftDate desc')SELECTc.ShiftStatisticsID,ShiftStatisticsCode,DepartmentCode,POSID,CashierCode,ShiftDate,TotalSalesAmount,CashAmount,SocialInsuranceAmount,UnionpayAmount,ValueCardAmount,WeChatAmount,AlipayAmount,CouponAmount,IsSend,AmountCheckCode,Operator,OperateDate,RemarkFROM [ShiftStatistics] c , #PageIndex PageIndexWHEREc.ShiftStatisticsID = PageIndex.ShiftStatisticsID ANDPageIndex.IndexID > @PageLowerBound ANDPageIndex.IndexID < @PageUpperBoundorder by ShiftDate descEXEC('SELECT COUNT(ShiftStatisticsID) AS TotalRecords FROM [ShiftStatistics] where 8=8'+@CONSQL)END
0 0