Examples of SQL Server stored procedures and cursors and related URLs

stored procedure instance with cursor includedThe first form of a notation

Gobeginif (object_id (' Pt_fault_report ', ' P ') is not null) drop proc Pt_fault_report; Endgocreate PROCEDURE pt_fault_report (@index int, @stop_time datetime, @start_time datetime) ASDECLARE@DEP artmentindex int, @departmentname nvarchar, @entitycount int, @allcount int, @fixtime int,@f Ixcount int;declare test_cur cursor FOR select field1, Field2, field3, field4 form table where [Email prot Ected] and field2> @stop_time or filed3< @start_time; OPEN test_cur; DECLARE @TableTemp TABLE (departmentindex int,departmentname nvarchar (+), entitycount int,allcount int,fixtime int, Fixcount int);--Define table variable FETCH NEXT from Test_cur to @departmentindex, @departmentname, @entitycount, @allcount, @fixtim   E, @fixcount; INsert @TableTemp values (@departmentindex, @departmentname, @entitycount, @allcount, @fixtime, @fixcount);-- Interpolate table variable while @ @FETCH_STATUS = 0BEGIN FETCH NEXT from Test_cur into @departmentindex, @departmentname,@entitycount, @allcount, @fixtime, @fixcount;   INsert @TableTemp values (@departmentindex, @departmentname, @entitycount, @allcount, @fixtime, @fixcount);
--select [email protected], [email protected],[email protected], [email protected], [email protected],[email protected]; END; CLOSe test_cur deallocate test_cur; SELECT * from @TableTemp;--The stored procedure returns data

Test stored Procedures

GO  DECLARE @starttime DATETIME, @endtime datetime;set @starttime =getdate (); set @endtime =getdate ()-30; EXEC Pt_fault_report 3, @starttime, @endtime;

The second type of cursor notation

Gobeginif (object_id (' Pt_fault_report ', ' P ') is not null)     drop proc Pt_fault_report; Endgocreate  PROCEDURE pt_fault_report (@business_index  INTEGER, @start_time  DATETIME, @stop_time  DATETIME, @ds_cur  cursor VARYING OUTPUT) asbegin    SET @ds_cur = cursor      forward_only STATIC for   Select Field1, Field2, field3, field4 form table where [email protected] and         field2> @stop_time or filed3< @start_time 
   ;open @ds_cur END;

Test stored Procedures

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;  

The Third way of writing (test method similar to the first kind)

Gobeginif (object_id (' Pt_fault_report ', ' P ') is not null)     drop proc Pt_fault_report; Endgocreate  PROCEDURE pt_fault_report (@business_index      int, @stop_time        DATETIME, @start_time       DATETIME) as   select * FROM table

1 Differences Between SQL Server table variables and temporal tables

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

2SQL Server stored procedure return, output parameters, and usage tips

Http://www.cnblogs.com/zhangchenliang/archive/2013/04/07/3006433.html

3sql2005 A stored procedure that returns a cursor parameter "cursor VARYING OUTPUT"

http://blog.csdn.net/paolei/article/details/6206004

4SqlServer cursors, stored procedures, and data block execution

http://blog.csdn.net/superhoy/article/details/7663542

SQL Server stored procedure and cursor related instances and related URLs