内含游标的存储过程实例

第一种写法

GO

BEGIN

IF (object_id('PT_FAULT_REPORT', 'P') is not null)

    drop proc PT_FAULT_REPORT;

END

GO

CREATE  PROCEDURE PT_FAULT_REPORT

(@index      int,

@stop_time        DATETIME,

@start_time       DATETIME)

AS

DECLARE

@departmentindex          int,

@departmentname   nvarchar(100),

@entitycount     int,

@allcount       int,

@fixtime         int,

@fixcount          int;

declare test_cur cursor for

   select field1, field2, field3, field4 form table where field1=@index and

         field2>@stop_time or filed3<@start_time  ;

OPEN test_cur;

DECLARE @TableTemp TABLE(departmentindex  int,departmentname nvarchar(100),entitycount  int,allcount int,fixtime  int,fixcount  int);--定义表变量

   FETCH NEXT FROM test_cur INTO @departmentindex, @departmentname, @entitycount, @allcount, @fixtime,@fixcount;

   INsert @TableTemp values(@departmentindex, @departmentname, @entitycount, @allcount, @fixtime,@fixcount);--给表变量插值

WHILE @@FETCH_STATUS = 0

BEGIN

  FETCH NEXT FROM test_cur INTO @departmentindex, @departmentname, @entitycount, @allcount, @fixtime,@fixcount;

   INsert @TableTemp values(@departmentindex, @departmentname, @entitycount, @allcount, @fixtime,@fixcount);  

  --select departmentindex=@departmentindex, departmentname=@departmentname,entitycount=@entitycount, allcount=@allcount, fixtime=@fixtime,fixcount=@fixcount;

END ;

 CLOSe test_cur

 Deallocate test_cur;

 select * from @TableTemp;--存储过程返回数据

测试存储过程

GO

DECLARE @starttime DATETIME,@endtime DATETIME;

set @starttime =GETDATE();

set @endtime=GETDATE()-30;

EXEC PT_FAULT_REPORT 3,@starttime,@endtime;

第二种传游标写法

 GO

BEGIN

IF (object_id('PT_FAULT_REPORT', 'P') is not null)

    drop proc PT_FAULT_REPORT;

END

GO

CREATE  PROCEDURE PT_FAULT_REPORT

(@business_index  INTEGER , @start_time  DATETIME , @stop_time  DATETIME, @ds_cur  CURSOR VARYING OUTPUT)

AS

BEGIN

    SET @ds_cur = CURSOR

    FORWARD_ONLY STATIC FOR

 select field1, field2, field3, field4 form table where field1=@index and

         field2>@stop_time or filed3<@start_time  ;

OPEN @ds_cur

END;

　　测试存储过程

GO

DECLARE @MyCursor CURSOR,@starttime DATETIME,@endtime DATETIME;

set @starttime =GETDATE();

set @endtime=GETDATE()-30;

EXEC PT_FAULT_REPORT 3,@starttime,@endtime,@ds_cur = @MyCursor OUTPUT;

FETCH NEXT FROM @MyCursor;

WHILE (@@FETCH_STATUS = 0)

BEGIN;

     FETCH NEXT FROM @MyCursor;

END;

CLOSE @MyCursor;

DEALLOCATE @MyCursor;

　　第三种写法(测试方法与第一种相似）

 GO

BEGIN

IF (object_id('PT_FAULT_REPORT', 'P') is not null)

    drop proc PT_FAULT_REPORT;

END

GO

CREATE  PROCEDURE PT_FAULT_REPORT

(@business_index      int,

@stop_time        DATETIME,

@start_time       DATETIME)

AS

   select * from table

1SQL Server 表变量和临时表的区别

http://www.cnblogs.com/kissdodog/archive/2013/07/03/3169470.html