SqlServer循环读取配置

最新推荐文章于 2024-09-19 07:17:33 发布

转载最新推荐文章于 2024-09-19 07:17:33 发布 · 53 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/muxueyuan/p/5306443.html

文章标签：

#数据库

本文介绍了一个SQL Server存储过程，用于查询特定教练在指定日期的预约情况。该过程通过循环遍历教练的所有预约时间配置，并计算每个时间段的预约数量，最终返回当天上午或下午是否有空闲预约时段。

USE [DB_JP_BaseInfo00]
GO
/****** Object:  StoredProcedure [dbo].[sp_wx_getAppointmentInfo_Str]    Script Date: 03/22/2016 14:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_wx_getAppointmentInfo_Str]
    @CoachID varchar(4000),
    @SearchTime datetime
AS
BEGIN
     DECLARE @temflag int         --结果
    Declare @TotalCount int      --某天某个号的预约次数
    declare @tem_shang int 
    declare @tem_xia int 
    declare @i int             --循环变量
    declare @count int         --循环次数      
    declare @Str nvarchar(max) --查询字符串
    declare @BeginTime_tem datetime
    declare @EndTime_Tem datetime
    declare @UserLimit_tem int
    declare @NoAppointmentConfig int --每个配置号对应的ID

   set @tem_shang=0
   set @tem_xia=0
   set @i=0
   select @count=count(*) from [CoachAppointmentTime] where CodeID=@CoachID;--查找教练的所有配置

      while @i<@count  --循环读取配置
    begin
    set @Str='select top 1 @NoAppointmentConfig=ID, @BeginTime_tem=Begintime,@EndTime_Tem=Endtime ,@UserLimit_tem=UserLimit from CoachAppointmentTime where ID not in (select top '+Str(@i) +' ID from CoachAppointmentTime order by ID asc) order by ID asc';--从临时表中获取
    EXEC sp_executesql @Str,N'@BeginTime_tem datetime output,@EndTime_Tem datetime output,@UserLimit_tem int output,@NoAppointmentConfig int output',@BeginTime_tem output,@EndTime_Tem output,@UserLimit_tem output,@NoAppointmentConfig output
    
    select @TotalCount=count(*) from StudentAppointmentTime where AppointmentDate=@SearchTime  and AppointmentTimeId=@NoAppointmentConfig --某天某个号的预约次数
    if(@UserLimit_tem>@TotalCount)
    begin
        if(@BeginTime_tem> '1900-01-01 12:00:00.000')
        set @tem_xia=1
    else if(@EndTime_Tem<'1900-01-01 12:00:00.000')
        set @tem_shang=1
    else
    begin 
    set @tem_xia=1;
    set @tem_shang=1;
    end
    end
    set @i=@i+1
    end
    
    if(@tem_shang=1 and @tem_xia=1)
    set @temflag=3
    else if(@tem_shang=1 and @tem_xia=0)
    set @temflag=1
    else if(@tem_shang=0 and @tem_xia=1)
    set @temflag=2
    --3 表示 上午和下午   1表示上午 2表示下午
    RETURN @temflag
END