SQL 大神快现身,帮小弟看一个SQL的问题

Question

先上 SQL 语句吧,一个存储过程.-- =============================================
-- Description: <考勤统计>
-- =============================================
ALTER PROCEDURE [dbo].[Attd_LeaveDataStat] 
-- Add the parameters for the stored procedure here
@StartDate DateTime, 
@EndDate DateTime,
@OrganIdSet nvarchar(2000),
@UserType int,
@GroupId int,
@OrganCode nvarchar(50),
@UserId int,
@UserName nvarchar(50),
@PageIndex int,
@PageSize int,
@OutRecordCount int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--上班时间
declare @startHH nvarchar(10),@startMM nvarchar(10),@endHH nvarchar(10),@endMM nvarchar(10),@startWorkTime Datetime,@endWorkTime datetime select @startHH = sMworkTimeH from dbo.Attd_WorkTimeConfig
select @startMM = sMWorkTimeM from dbo.Attd_WorkTimeConfig
select @endHH = eAWorkTimeH from dbo.Attd_WorkTimeConfig
select @endMM = eAWorkTimeM from dbo.Attd_WorkTimeConfig set @startWorkTime = cast(convert(char(10),getDate(),120) + ' ' + @startHH + ':' + @startMM + ':00' as datetime) set @endWorkTime = cast(convert(char(10),getDate(),120) + ' ' + @endHH + ':' + @endMM + ':00' as datetime)

Create Table #LeaveStatBaseTable
(
UserId int null,
UserCodeId nvarchar(50) null,
UserName nvarchar(50) null,
OrganId int null,
OrganCodeId nvarchar(50) null,
OrganName nvarchar(200) null,
MustPunchNum int null,
ActualPunchNum int null,
NotPunchNum int null,
AbsenteeismNum int null,
DiseaseLeaveNum int null,--病假
PersionLeaveNum int null,--事假
MarryLeaveNum int null,--婚假
BornLeaveNum int null,--产假
TravelLeaveNum int null, --公差
GongXiuNum int null,
BuRuNum int null,
SangJiaNum int null,
TanQinNum int null,
OverTimeWork int null -- 加班
)
if(@OrganIdSet <> '-1')
begin

if(@UserType <> -1)
begin
insert into #LeaveStatBaseTable
(
UserId,
UserCodeId,
UserName,
OrganId,
OrganCodeId,
OrganName,
MustPunchNum,
ActualPunchNum,
NotPunchNum,
AbsenteeismNum,
DiseaseLeaveNum,--病假
PersionLeaveNum,--事假
MarryLeaveNum,--婚假
BornLeaveNum,--产假
TravelLeaveNum, --公差
GongXiuNum, --公休
BuRuNum,
SangJiaNum,
TanQinNum,
OverTimeWork
)
select GscUser.TableIdOfGscUser,
GscUser.GscUserCodeId,
GscUser.GscUserName,
GscOrgan.TableIdOfGscOrgan,
GscOrgan.GscOrganCodeId,
GscOrgan.GscOrganName,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
from GscOrgan inner join GscUser on GscOrgan.GscOrganCodeId = GscUser.GscOrganCodeId 
and GscUser.GscUserName like '%'+@UserName+'%'
where GscOrgan.TableIdOfGscOrgan in (select * from dbo.GetOrganList_Fun(@UserType,@UserId,@GroupId,@OrganCode))
end
else
begin
insert into #LeaveStatBaseTable
(
UserId,
UserCodeId,
UserName,
OrganId,
OrganCodeId,
OrganName,
MustPunchNum,
ActualPunchNum,
NotPunchNum,
AbsenteeismNum,
DiseaseLeaveNum,--病假
PersionLeaveNum,--事假
MarryLeaveNum,--婚假
BornLeaveNum,--产假
TravelLeaveNum, --公差
GongXiuNum,
BuRuNum,
SangJiaNum,
TanQinNum,
OverTimeWork
)
select GscUser.TableIdOfGscUser,
GscUser.GscUserCodeId,
GscUser.GscUserName,
GscOrgan.TableIdOfGscOrgan,
GscOrgan.GscOrganCodeId,
GscOrgan.GscOrganName,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
from GscOrgan inner join GscUser on GscOrgan.GscOrganCodeId = GscUser.GscOrganCodeId  where GscOrgan.TableIdOfGscOrgan in (@OrganIdSet) and GscUser.GscUserName like '%'+@UserName+'%'--单位ID
end
end else if(@UserId <> 0)
begin
--离开状态
insert into #LeaveStatBaseTable
(
UserId,
UserCodeId,
UserName,
OrganId,
OrganCodeId,
OrganName,
MustPunchNum,
ActualPunchNum,
NotPunchNum,
AbsenteeismNum,
DiseaseLeaveNum,--病假
PersionLeaveNum,--事假
MarryLeaveNum,--婚假
BornLeaveNum,--产假
TravelLeaveNum, --公差
GongXiuNum,
BuRuNum,
SangJiaNum,
TanQinNum,
OverTimeWork
)
select GscUser.TableIdOfGscUser,
GscUser.GscUserCodeId,
GscUser.GscUserName,
GscOrgan.TableIdOfGscOrgan,
GscOrgan.GscOrganCodeId,
GscOrgan.GscOrganName,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
from GscOrgan inner join GscUser on GscOrgan.GscOrganCodeId = GscUser.GscOrganCodeId  where GscUser.TableIdOfGscUser = @UserId and GscUser.GscUserName like '%'+@UserName+'%'
end Declare @WorkDays int select @WorkDays = dbo.GetVac_20081021(@StartDate,dateadd(day,1,@EndDate)) update #LeaveStatBaseTable
set MustPunchNum = @WorkDays * 2 Create Table #UserCheckOutList
(
UserId int null,
CheckTime DateTime null,
IsWorkDay int null,
OverTimeWork int null
)
insert into #UserCheckOutList
(
UserId,
CheckTime
)
select GscUser.TableIdOfGscUser,
Attd_CheckOut.CheckTime
from GscUser inner join #LeaveStatBaseTable on GscUser.TableIdOfGscUser = #LeaveStatBaseTable.UserId
inner join Attd_UserInfo on GscUser.VerCodeIdOfUpdateGscAppTableField = Attd_UserInfo.Badgenumber
inner join Attd_CheckOut on Attd_UserInfo.UserId = Attd_CheckOut.UserId
where Attd_CheckOut.CheckTime between @StartDate and @EndDate update #UserCheckOutList
set IsWorkDay = [dbo].DateIsWorkDay (CheckTime) update #UserCheckOutList
set OverTimeWork = case when datediff(mi,cast((convert(nvarchar(11),CheckTime,120) + '17:00:00') as datetime),CheckTime) > 0 then datediff(mi,cast((convert(nvarchar(11),CheckTime,120) + '17:00:00') as datetime),CheckTime) else 0 end Create Table #UserCheckStatTable
(
UserId int null,
CheckDate datetime null,
CheckNum int null,
OverTimeWork int null
)
insert into #UserCheckStatTable
(
UserId,
CheckDate,
CheckNum,
OverTimeWork
)
select UserId,
convert(char(10),CheckTime,120),
isNull(case when count(*) > 2 then 2 else count(*) end,0),
sum(OverTimeWork)
from #UserCheckOutList
group by UserId,convert(char(10),CheckTime,120) Create Table #WorkDayList
(
WorkDay DateTime null
)
insert into #WorkDayList
(
WorkDay
)
select * from [dbo].[GetWorkDayList]
(
@StartDate, 
@EndDate
)
Create Table #UserAbsenteeismList
(
UserId int null,
WorkDay DateTime null,
CheckOutNum int null,
OverTimeWorkNum int null,
IsLeave int null,
isAbsenteeism int null
)
insert into #UserAbsenteeismList
(
UserId,
WorkDay,
CheckOutNum,
OverTimeWorkNum,
IsLeave,
isAbsenteeism
)
select #LeaveStatBaseTable.UserId,
#WorkDayList.WorkDay,
0,
0,
0,
-1
from #LeaveStatBaseTable,#WorkDayList update #UserAbsenteeismList
set CheckOutNum = #UserCheckStatTable.CheckNum,
OverTimeWorkNum = #UserCheckStatTable.OverTimeWork
from #UserAbsenteeismList inner join #UserCheckStatTable
on #UserAbsenteeismList.UserId = #UserCheckStatTable.UserId
and datediff(day,#UserAbsenteeismList.WorkDay,#UserCheckStatTable.CheckDate) = 0 update #UserAbsenteeismList
set IsLeave = 1
from #UserAbsenteeismList inner join Attd_NewLeaveInfo on #UserAbsenteeismList.UserId = Attd_NewLeaveInfo.LeaveUser
inner join Attd_LeaveTimeList on Attd_NewLeaveInfo.LeaveId = Attd_LeaveTimeList.LeaveId
where #UserAbsenteeismList.CheckOutNum = 0
and (datediff(day,Attd_LeaveTimeList.StartTime,#UserAbsenteeismList.WorkDay) >= 0
and datediff(day,#UserAbsenteeismList.WorkDay,Attd_LeaveTimeList.EndTime) >= 0) update #UserAbsenteeismList
set isAbsenteeism = case when CheckOutNum = 0 and IsLeave = 0 and ((datediff(day,WorkDay,getDate()) = 0 and datediff(minute,@endWorkTime,getDate()) > 0) or (datediff(day,WorkDay,getDate()) <> 0)) then 1 else 0 end Create Table #UserAbsenteeismStat
(
UserId int null,
UserOverWorkNum int null,
UserAbsenteeismNum int null,
UserActualNum int null
) insert into #UserAbsenteeismStat
(
UserId,
UserOverWorkNum,
UserAbsenteeismNum,
UserActualNum
)
select UserId,
isnull(sum(OverTimeWorkNum),0),
isnull(count(case when isAbsenteeism = 1 then 1 else null end),0),
isnull(sum(CheckOutNum),0)
from #UserAbsenteeismList
group by UserId

update #LeaveStatBaseTable
set AbsenteeismNum = #UserAbsenteeismStat.UserAbsenteeismNum,
OverTimeWork = #UserAbsenteeismStat.UserOverWorkNum
from #LeaveStatBaseTable inner join #UserAbsenteeismStat
on #LeaveStatBaseTable.UserId = #UserAbsenteeismStat.UserId update #LeaveStatBaseTable
set ActualPunchNum = UserActualNum
from #LeaveStatBaseTable inner join #UserAbsenteeismStat
on #LeaveStatBaseTable.UserId = #UserAbsenteeismStat.UserId

if(getDate() >= @StartDate and getDate() <= @EndDate)
begin
if(datediff(minute,@startWorkTime,getDate()) <= 0)--未上班
begin

update #LeaveStatBaseTable
set MustPunchNum = MustPunchNum - 2 end else if(datediff(minute,@endWorkTime,getDate()) <= 0)--未下班
begin update #LeaveStatBaseTable
set MustPunchNum = MustPunchNum - 1 end end update #LeaveStatBaseTable
set NotPunchNum = MustPunchNum - ActualPunchNum Create Table #UserLeaveStatTable
(
UserId int null,
DiseaseLeaveNum int null,
PersionLeaveNum int null,
MarryLeaveNum int null,
BornLeaveNum int null,
TravelLeaveNum int null,
GongXiuNum int null,
BuRuNum int null,
SangJiaNum int null,
TanQinNum int null
)
一个很杯具的问题就是,在这里面 我不知道在哪里去添加一个

Answer 1

上面是一部分,下面还有一点点--/*
insert into #UserLeaveStatTable
(
UserId,
DiseaseLeaveNum,
PersionLeaveNum,
MarryLeaveNum,
BornLeaveNum,
TravelLeaveNum,
GongXiuNum,
BuRuNum,
SangJiaNum,
TanQinNum
)
select LeaveUser,
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 12 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 13 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 14 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 17 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 18 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 19 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 20 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 21 then 1 else null end),0),
isnull(count(case when (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate) and LeaveType = 22 then 1 else null end),0)
from Attd_NewLeaveInfo inner join Attd_LeaveTimeList on Attd_NewLeaveInfo.LeaveId = Attd_LeaveTimeList.LeaveId 
where (Attd_LeaveTimeList.StartTime between @StartDate and @EndDate or Attd_LeaveTimeList.EndTime between @StartDate and @EndDate)
and LeaveUser in
(select UserId from #LeaveStatBaseTable) 
group by LeaveUser
update #LeaveStatBaseTable
set #LeaveStatBaseTable.DiseaseLeaveNum = #UserLeaveStatTable.DiseaseLeaveNum,
#LeaveStatBaseTable.PersionLeaveNum = #UserLeaveStatTable.PersionLeaveNum,
#LeaveStatBaseTable.MarryLeaveNum = #UserLeaveStatTable.MarryLeaveNum,
#LeaveStatBaseTable.BornLeaveNum = #UserLeaveStatTable.BornLeaveNum,
#LeaveStatBaseTable.TravelLeaveNum = #UserLeaveStatTable.TravelLeaveNum, #LeaveStatBaseTable.GongXiuNum = #UserLeaveStatTable.GongXiuNum,
#LeaveStatBaseTable.BuRuNum = #UserLeaveStatTable.BuRuNum,
#LeaveStatBaseTable.SangJiaNum = #UserLeaveStatTable.SangJiaNum,
#LeaveStatBaseTable.TanQinNum = #UserLeaveStatTable.TanQinNum
from #LeaveStatBaseTable inner join #UserLeaveStatTable on #LeaveStatBaseTable.UserId = #UserLeaveStatTable.UserId
--*/
select * from 
(
select row_number() over(order by UserName asc) as Rows,
UserName,
OrganName,
MustPunchNum,
ActualPunchNum,
NotPunchNum,
AbsenteeismNum,
DiseaseLeaveNum,
PersionLeaveNum,
MarryLeaveNum,
BornLeaveNum,
TravelLeaveNum,
GongXiuNum,
BuRuNum,
SangJiaNum,
TanQinNum,
OverTimeWork
from #LeaveStatBaseTable
) as t where Rows between ((@PageIndex-1)*@PageSize+1) and (@PageIndex*@PageSize)select @OutRecordCount = count(UserName) from #LeaveStatBaseTabledrop table #LeaveStatBaseTable,#UserCheckOutList,#UserCheckStatTable,#WorkDayList,#UserAbsenteeismList,#UserAbsenteeismStat,#UserLeaveStatTable
END

Answer 2

在这里我想说一下就是,不是小弟不知道查,是确实对临时表这块不是很了解这个是以前的一个哥们写的,我现在把 @UserName 穿进去了,但是不知道放在那个位置才好,
而且根据这个存储过程根本就查不到数据,我很纠结,求大神 给点思路,或者是想法.在此感谢了!项目很着急,我也着急啊.

Answer 3

自己学会调试存储过程：Declare @UserName Nvarchar(100)
Set @UserName='ffff'
begin tran
业务逻辑Rollback

Answer 4

够长,够爽,够看会儿得.学习ing...

Answer 5

执行几个select ALTER PROCEDURE [dbo].[Attd_LeaveDataStat] 
    -- Add the parameters for the stored procedure here
    @StartDate DateTime, 
    @EndDate DateTime,
    @OrganIdSet nvarchar(2000),
    @UserType int,
    @GroupId int,
    @OrganCode nvarchar(50),    
    @UserId int,
    @UserName nvarchar(50),
    @PageIndex int,
    @PageSize int,
    @OutRecordCount int output
AS
BEGIN这个一段改成 declare
  @StartDate DateTime, 
    @EndDate DateTime,
    @OrganIdSet nvarchar(2000),
    @UserType int,
    @GroupId int,
    @OrganCode nvarchar(50),    
    @UserId int,
    @UserName nvarchar(50),
    @PageIndex int,
    @PageSize int,
    @OutRecordCount int output把语句最后面的end 删掉
然后给变量赋值然后从上往下的选择部分语句执行，看到那一段给弄没有了。

Answer 6

存储过程是没有错的,只是一个逻辑问题 , 咳, 我现在不知道 该把 @UserName 加在那里,这个字段和参数都是新加的主要是要做一个搜索的功能,把@UserName传进去,从上面的存储过程中,肯定是能够看到@UserName,但是那个添加的位置 我不敢肯定是不是对的,获取有80%的正确性,我看了一下大家的回复,主要是我没有把问题描述清楚问题就是  我现在不知道应该把 @UserName 这个参数写在存储过程那个地方,调用存储过程 我也知道,SQL Server Profiler 我也去跟踪了一下这个存储过程和所触发的事件的存储过程,但是最终得到的结果是没有数据,
这个问题 困扰了我很久,我从昨天晚上加班到现在一直搞不定,这个项目不能 F5 进行调试,也就是说不能逐步逐步的去检测哪个地方出错,或者是那个SQL的地方出错用SQL Server Profiler 进行跟踪的时候把那个运行存储过程的那段SQL拿出来运行,还是得不到数据,
我很纠结.希望各位 细心的看一下这个存储过程,这个是我第一次遇到这么大 这么长的 存储过程.
还涉及到了很多的临时表,唉 ...  我真的快崩溃了

Answer 7

传入参数 UserID和UserName有关联吗？

Answer 8

我是说@UserID是@UserName对应的ID吗？如果是，又是同一张表那你这个Username传进来就没有意义，通过UserId就可以检索出你想要的数据了，如果不是，那么加进去Username必然没有结果的啊