USE [ccnu]

GO

/****** Object:  StoredProcedure [dbo].[P_ADD_DATA_XSBLHYCQK]    Script Date: 2015/2/13 10:32:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Zen>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[P_ADD_DATA_XSBLHYCQK]

AS

declare @fdy varchar(200),@xs_id varchar(200),@bzr varchar(200),@sex varchar(200);

declare @kind varchar(200),@ssbh varchar(200),@xyan varchar(200),@class varchar(200);

declare @bbq varchar(200);

declare @swsc numeric; -- 上网时长

declare @ZHHSSSJ time; -- 最后回宿舍时间

declare @day int ;-- 天数

DECLARE @RandomNumber float

DECLARE @RandomInteger int;

DECLARE @MaxValue int;

DECLARE @MinValue int;

BEGIN

    set @day = 0;

    --打开游标

    while @day<14

    begin

        select @bbq=CONVERT(nvarchar(8),cast('2015-02-01' as datetime) +@day,112)

        declare cur_JQLXXX CURSOR FOR SELECT [FDY]

          ,[BZR]

          ,[CLASS]

          ,[SSBH]

          ,[SEX]

          ,[ID]

          ,[KIND]

          ,[DEPT]

      FROM [ccnu].[dbo].[WG_JQLXXX0212];

        open cur_JQLXXX

        --开始循环游标变量

         FETCH NEXT FROM cur_JQLXXX INTO @fdy,@bzr,@class,@ssbh,@sex,@xs_id,@kind,@class

         WHILE @@FETCH_STATUS = 0

          begin

            SET @MaxValue = 5*60*60

            SET @MinValue = 1*60*60

            SELECT @RandomNumber = RAND()

            SELECT @swsc = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

            select @ZHHSSSJ=dateadd(second,ceiling(rand() * 25200)+61200,cast('2015-02-01' as datetime)+@day)

           --执行sql操作

             insert into [ccnu].[dbo].WG_XSBLHYCQK0212([BBQ]

              ,[XS_ID]

              ,[XB]

              ,[XS_TYPE]

              ,[SUSE_ID]

              ,[XYUAN]

              ,[ZYE]

              ,[BJI]

              ,[BZR]

              ,[FDY]

              ,[SFWG]

              ,[SFYC]

              ,[SWZSC]

              ,[FZCSDSWSC]

              ,[YCKSRQ]

              ,[YCJSRQ]

              ,[ZHHSSSJ])

          select  @bbq,@xs_id,@sex,@kind,@ssbh,@xyan,NULL,@class,@bzr,@fdy,0,0,@swsc,0,null,null,@ZHHSSSJ

          FETCH NEXT FROM cur_JQLXXX INTO @fdy,@bzr,@class,@ssbh,@sex,@xs_id,@kind,@class --取下一条数据

        end

        CLOSE cur_JQLXXX  --关闭游标

        deallocate cur_JQLXXX --释放游标，写循环嵌套的时候一定要注意内层游标一定要在外层游标的包含内声明、关闭或释放。

    select @day=@day+1

    end

END

GO