储存过程实例

 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[proST_WAS_R]
 -- Add the parameters for the stored procedure here
  @startDate datetime,
     @endDate datetime,
     @returnValue int output
AS
BEGIN
if not exists(select * from sysobjects where name='ST_WAS_R')
begin
  CREATE TABLE [dbo].[ST_WAS_R](
 [STCD] [int] NOT NULL,
 [TM] [datetime] NOT NULL,
 [UPZ] [numeric](7, 3) NULL,
 [DWZ] [numeric](7, 3) NULL,
 [TGTQ] [numeric](9, 3) NULL,
 [SWCHRCD] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
 [SUPWPTN] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
 [SDWWPTN] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_ST_WAS_R] PRIMARY KEY CLUSTERED
(
 [STCD] ASC,
 [TM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
end
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 declare @count int,
 
   @stcd varchar(16),
   @i int,--判断循环次数

   @insertDate datetime,--插入的日期
   @UPZ float,
 @DWZ float,
 @TGTQ float,
 @SWCHRCD char(1),
 @SUPWPTN char(1),
 @SDWWPTN char(1)

 SELECT    @count= DATEDIFF(day, @startDate, @endDate)
 FROM         test

 set @i =0

 
    set @UPZ =1
 set @DWZ =1
 set @TGTQ =1
 set @SWCHRCD =1
 set @SUPWPTN =1
 set @SDWWPTN =1
    set @returnValue=1--输出参数

DECLARE authors_cursor CURSOR FOR
select id from test

open authors_cursor
FETCH NEXT FROM authors_cursor INTO @stcd

WHILE @@FETCH_STATUS = 0
begin
 
 while (@i<=@count)
    begin 
          set @i = 0  

         set @insertDate = @startDate
     insert   into   ST_WAS_R(STCD,TM,UPZ,DWZ,TGTQ,SWCHRCD,SUPWPTN,SDWWPTN) 
           values(@stcd,@insertDate,@UPZ,@DWZ,@TGTQ,@SWCHRCD,@SUPWPTN,@SDWWPTN)
          
   if(@@error<>0)
     begin
    set @returnValue=0
    return @returnValue
     end
           SELECT   @insertDate = DATEADD(day, 1, @insertDate)
           FROM     test
          
       
          set @i = @i + 1
   if(@i%10=0)
   begin
    set @UPZ =@UPZ+1
    set @DWZ =@DWZ+1
    set @TGTQ =@TGTQ+1
   end
       
    end

 FETCH NEXT FROM authors_cursor
 INTO @stcd

end
CLOSE authors_cursor --关闭游标
DEALLOCATE authors_cursor --释放游标
   
END