The use of result sets and cursors in SQL Server [summary]

I. Solutions to use the result set

You can use the following statement to use the result set of another stored procedure in a stored procedure or to re-use the execution result of a dynamic SQL statement:

 
Insert IntoTable_nameExecProcedure_name@ Parameters_var
 

UseCodeAs follows:

 --  1. Create a temporary table # TMP. The table structure is compatible with the returned result set of procedure_name in the target Stored Procedure (compatible, not the same ).  Create   Table  # TMP (  [  Columns_list  ] )  --  2.1 execute the stored procedure and insert the returned result set of the stored procedure into the temporary table.  Insert   Into # TMP Exec Procedure_name @ Parameters_var  --  2.2 The execution of dynamic SQL statements is as follows (if you execute dynamic SQL statements directly through exec, the SQL statement has a length limit of 4 K .)  Insert   Into # TMP Exec DBO. sp_executesql @ Querystring_var  -- 3. Now you can use (filter, change or search) # TMP.  If   Exists ( Select   *   From  # TMP)  Begin         --  Execution Branch 1      End   Else       Begin         --  Execution Branch 2      End  -- 4. Finally, clear the temporary table.  Drop   Table # TMP

Idea 2: Create a function to return the desired query result set and use it as a table in the stored procedure.

Reference Source: How to Use the result set returned by another stored procedure in a stored procedure

Ii. Try to use the cursor

The following is an example of using SQL Server cursor. First, create a test environment:

 Create   Table # TMP (ID Int , Username Varchar ( 12  ))  Go  Insert  Into # TMP Values ( 1 , '  Aaaa  '  );  Insert   Into # TMP Values ( 2 , '  Bbbb  '  ); Insert   Into # TMP Values ( 3 , '  AABB  '  );  Insert   Into # TMP Values ( 4 , '  Bbaa  ' );  Go  Select   *   From  # TMP  Go 

The Code is as follows:

 Alter   Procedure  Demo  As  Begin      Declare   @ Tmpid   Int , @ Tmpname   Varchar ( 12  );  Begin  Try  --  Note: order by statements cannot use parentheses for select statements.          Declare Cr Cursor   For   Select ID, username From # TMP Order   By  ID;  Open CR;  Fetch   Next   From Cr Into   @ Tmpid , @ Tmpname  ;  --  @ Fetch_status returns the status of the fetch statement of the currently opened cursor.          While ( @ Fetch_status  =  0  ) Begin             Print   Convert ( Varchar ( 6 ), @ Tmpid ) +   @ Tmpname  ;  Fetch   Next   From Cr Into   @ Tmpid , @ Tmpname ;--  Read the next data             If ( @ Tmpname   =   '  AABB  ' ) Goto  Usergo;  End  ; Usergo:  Print   '  Custom GOTO statement jump  '  ; Close CR; --  Close cursor          Deallocate CR; --  Release cursor      End  Try  Begin  Catch  Close CR; --  Close cursor          Deallocate CR; -- Release cursor          Print   '  Exception Handling!  '      End  Catch  End  ;  Exec Demo

Refer to msdn resources: http://msdn.microsoft.com/zh-cn/library/ms180169.aspx

(End)