sql 存储过程,该如何解决

2013-01-01

sql存储过程create proc leaveoff@year varchar(20),@month varchar(20),@dateStart datetime,@dateEnd da

sql 存储过程


create proc leaveoff
@year varchar(20),
@month varchar(20),
@dateStart datetime,
@dateEnd datetime
as 
declare @days int --时间段内的天数 
declare @date datetime
declare @date1 datetime
declare @condition varchar(200)
set @days = datediff (day,@dateStart,@dateEnd)+1--计算时间段内天数 
set @date = convert(datetime,(@year+'-'+@month+'-'+convert(varchar(20),@days)))--得出最后一天
print @date
print @year
print @month
set @date1 = convert(datetime,(@year+'-'+@month+'-01'))--得出第一天

set @condition=(select 
(case 
when(datepart(year,PeopleLeave.leaveStartTime)=@year and datepart(month,PeopleLeave.leaveStartTime)=@month and 
datepart(year,PeopleLeave.leaveEndTime)=@year and datepart(month,PeopleLeave.leaveEndTime)=@month)
then ('考勤日期  between  '''+ CONVERT(varchar(12) , leaveStartTime, 102 ) +'''  and   '''+ CONVERT(varchar(12) , leaveEndTime, 102 )+'''')
when (datepart(year,PeopleLeave.leaveStartTime)=@year and datepart(month,PeopleLeave.leaveStartTime)=@month and 
datepart(year,PeopleLeave.leaveEndTime)=@year and datepart(month,PeopleLeave.leaveEndTime)>@month)
then ('考勤日期  between  '''+ CONVERT(varchar(12) , leaveStartTime, 102 ) +'  and   '+CONVERT(varchar(12) , @date, 102 )+'''')
when (datepart(year,PeopleLeave.leaveStartTime)=@year and datepart(month,PeopleLeave.leaveStartTime)<@month and 
datepart(year,PeopleLeave.leaveEndTime)=@year and datepart(month,PeopleLeave.leaveEndTime)=@month)
then ('考勤日期  between  '''+ CONVERT(varchar(12) , @date1, 102 ) +'  and   '+ CONVERT(varchar(12) , leaveEndTime, 102 )+'''')
end) condion from PeopleLeave)
print @condition

update temp set 考勤结果='请假' where 人员编号
in(select pid from PeopleLeave) and @condition

我得出的结果是print @condition的结果是：考勤日期 between '2012.11.01' and '2012.11.04'
但是现在报了个错误提示如下：在应使用条件的上下文(在 '@condition' 附近)中指定了非布尔类型的表达式。求解
[解决办法]
set @condition='update temp set 考勤结果=''请假'' where 人员编号 in(select pid from PeopleLeave) and'+@condition
exec(@condition)
[解决办法]

CREATE PROC leaveoff
    @year VARCHAR(20) ,
    @month VARCHAR(20) ,
    @dateStart DATETIME ,
    @dateEnd DATETIME
AS
    DECLARE @days INT --时间段内的天数
    DECLARE @date DATETIME
    DECLARE @date1 DATETIME
    DECLARE @condition VARCHAR(200)
    SET @days = DATEDIFF(day, @dateStart, @dateEnd) + 1--计算时间段内天数
    SET @date = CONVERT(DATETIME, ( @year + '-' + @month + '-'
                                    + CONVERT(VARCHAR(20), @days) ))--得出最后一天

    PRINT @date
    PRINT @year
    PRINT @month
    SET @date1 = CONVERT(DATETIME, ( @year + '-' + @month + '-01' ))--得出第一天

    SET @condition = ( SELECT   ( CASE WHEN ( DATEPART(year,
                                                       PeopleLeave.leaveStartTime) = @year
                                              AND DATEPART(month,
                                                           PeopleLeave.leaveStartTime) = @month
                                              AND DATEPART(year,
                                                           PeopleLeave.leaveEndTime) = @year
                                              AND DATEPART(month,
                                                           PeopleLeave.leaveEndTime) = @month
                                            )
                                       THEN ( '考勤日期  between  '''
                                              + CONVERT(VARCHAR(12), leaveStartTime, 102)
                                              + '''  and   '''

                                              + CONVERT(VARCHAR(12), leaveEndTime, 102)
                                              + '''' )
                                       WHEN ( DATEPART(year,
                                                       PeopleLeave.leaveStartTime) = @year
                                              AND DATEPART(month,
                                                           PeopleLeave.leaveStartTime) = @month
                                              AND DATEPART(year,
                                                           PeopleLeave.leaveEndTime) = @year
                                              AND DATEPART(month,
                                                           PeopleLeave.leaveEndTime) > @month
                                            )
                                       THEN ( '考勤日期  between  '''
                                              + CONVERT(VARCHAR(12), leaveStartTime, 102)

                                              + '  and   '
                                              + CONVERT(VARCHAR(12), @date, 102)
                                              + '''' )
                                       WHEN ( DATEPART(year,
                                                       PeopleLeave.leaveStartTime) = @year
                                              AND DATEPART(month,
                                                           PeopleLeave.leaveStartTime) < @month
                                              AND DATEPART(year,
                                                           PeopleLeave.leaveEndTime) = @year
                                              AND DATEPART(month,
                                                           PeopleLeave.leaveEndTime) = @month
                                            )
                                       THEN ( '考勤日期  between  '''

                                              + CONVERT(VARCHAR(12), @date1, 102)
                                              + '  and   '
                                              + CONVERT(VARCHAR(12), leaveEndTime, 102)
                                              + '''' )
                                  END ) condion
                       FROM     PeopleLeave
                     )
    PRINT @condition
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'update temp set 考勤结果=''请假'' where 人员编号
in(select pid from PeopleLeave) and ' + @condition

    EXEC (@sql)

热点排行

SQL Server

sql 存储过程,该如何解决