How to create and use dynamic cursors for SQLSREVER

▲ Create a cursor

CREATE PROCEDURE Usp_CreateCursor (@Select_Command varchar (8000), @ Cursor_Return CURSOR VARYING OUTPUT) AS / * stored procedure name: Usp_CreateCursor Function Description: The description specified SELECT create a dynamic cursor parameters: @Select_Command --- SELECT statement; @Cursor_Return - - To return to the cursor variable idea: the key to the dynamic cursor is to see how to construct its SELECT statement, because SELECT is a string table, definitions cannot be used directly, but it can come from the table. So my purpose is to create a unified table, which is not available from the data. The table has a certain grammar rule, so it should be generated according to the list list, which can be obtained from the system table. The key question is how to insert the data into the temporary table, I figure out a statement to implement this feature, that is, INSERT INTO Execute , and SQL7.0 help not speak. You can create data with data. Creation: Kang Kangmin Create Date: 2001-07-11 * / Declare @select_command_temp varchar (8000), --- Store SELECT Temporary Syntax @Table_list varchar (255), --- Store list @column_list varchar (8000), --- Storage field list @table_name varchar (30), - store separate table name @column_name varchar (30), --- Store a separate field name (but may be *) @column_syntax varchar (8000), --- Syntax (integrated) @Column_name_temp varchar (30), --- store field name @column_type_temp varchar (30), --- Storage field type @column_syntax_temp varchar (8000), --- Syntax (single) @Column_Length_Temp int, --- storage field length @Column_XPREC_TEMP INT, --- Storage field accuracy @Column_xscale_temp int, --- Storage field count number @ From_pos int, --- Store from the future @where_pos int, --- Store WHERE position @Having_pos int, --- Store Having location @GroupBy_pos int, --- Store GroupBy position @orderby_pos int, --- Store ORDERBY position @Temp_pos int, --- Temporary variable @Column_count int

--- Create a temporary table Create Table #test (a int) --- If the selected SELECT statement does not start with 'SELECT', automatically modify if left (Ltrim (@select_command), 6) <> SELECT 'Select @Select_Command =' Select ' @Select_Command --- the beginning of the' SELECT 'removed Select @Select_Command_Temp = Lower (Ltrim (@Select_Command)) If Left (@ Select_Command_Temp, 6) =' select 'Select @Select_Command_Temp = Right ( @ Select_Command_Temp, Len (@Select_Command_Temp) - 7) --- take respective positions reserved words, to obtain a list of tables Select @From_Pos = CHARINDEX ( 'from', @ Select_Command_Temp) Select @Where_Pos = CHARINDEX ( 'where', @ Select_Command_Temp ) Select @Having_Pos = CHARINDEX ( 'having', @ Select_Command_Temp) Select @Groupby_Pos = CHARINDEX ( 'groupby', @ Select_Command_Temp) Select @Orderby_Pos = CHARINDEX ( 'orderby', @ Select_Command_Temp) If @Where_Pos> 0 Select @Temp_Pos = @ Where_PosIf @Having_Pos> 0 And @Having_Pos <@Temp_Pos Select @Temp_Pos = @Having_PosIf @Groupby_Pos> 0 And @Groupby_Pos <@Temp_Pos Select @Temp_Pos = @Groupby_PosIf @Orderby_Pos> 0 And @Orderby_Pos <@Temp_Pos Select @Temp_Pos = @ Orderby_Pos- Take a table Table If @Temp_Pos> 0 Begin Select @Table_List = SUBSTRING (@ Select_Command_Temp, @ From_Pos 6, @ Temp_Pos - @From_Pos - 1) EndElse Begin Select @Table_List = SUBSTRING (@ Select_Command_Temp, @ From_Pos 6, Len (@Select_Command_Temp) @From_pos - 1) end

Select @Column_Syntax = '' --- only lists fields Select @Select_Command_Temp = Left (@ Select_Command_Temp, @ From_Pos - 1) While Len (@Select_Command_Temp)> 0 Begin --- taking comma position Select @Temp_Pos = CHARINDEX ( ' , ', @ Select_Command_Temp) --- the first time to take the field names If @Temp_Pos> 0 Begin Select @Column_Name = Left (@ Select_Command_Temp, @ Temp_Pos - 1) End Else Begin Select @Column_Name = @Select_Command_Temp End --- table and take The field name (may be '*') if charIndex ('.', @ Colorn_name)> 0 begin select @table_name = left (@ Column_name, Charindex ('.', @ Colorn_name) - 1) SELECT @Column_name = Right ( @ Column_name, LEN (@column_name) - Charindex ('.', @ Column_name) END ELSE BEGIN SELECT @Table_name = @table_list end

--- The field appears' * 'if charIndex (' * ', @ Column_name)> 0 Begin select @column_name =' SELECT @LOOP_SEQ = 1 - - Column number SELECT @Column_count = count (*) from FROM SysColumns Where Id = object_Id (@Table_name) While @Loop_Seq <= @Column_Count Begin --- take field name, field type, length, precision, decimal Select @Column_Name_Temp = SysColumns.Name, @Column_Type_Temp = Lower (SysTypes. name), @Column_Length_Temp = SysColumns.Length, @Column_Xprec_Temp = SysColumns.Xprec, @Column_Xscale_Temp = SysColumns.Xscale From SysColumns, SysTypes Where SysColumns.Id = object_Id (@Table_name) And SysColumns.Colid = @Loop_Seq And SysColumns.XuserType = SysTypes .XuserType --- field formed syntax expressions Select @Column_Syntax_Temp = Case When @Column_Type_Temp In ( 'datetime', 'image', 'int') Then @Column_Name_Temp '' @Column_Type_Temp When @Co lumn_Type_Temp In ( 'binary', 'bit', 'char', 'varchar') Then @Column_Name_Temp '' @Column_Type_Temp '(' Convert (Varchar (10), @ Column_Length_Temp) ')' Else @Column_Name_Temp '' @Column_type_temp '(' convert) ',' Convert (varchar (10), @ color (@ Column_xscale_temp) ')'

End Select @Column_Syntax = @Column_Syntax @Column_Syntax_Temp ',' Select @Loop_Seq = @loop_Seq 1 End End Else Begin --- take field name Select @Column_Name_Temp = @Column_Name --- take field type, length, precision , decimal Select @Column_Type_Temp = Lower (SysTypes.Name), @Column_Length_Temp = Isnull (SysColumns.Length, 0), @Column_Xprec_Temp = Isnull (SysColumns.Xprec, 0), @Column_Xscale_Temp = Isnull (SysColumns.Xscale, 0) From SysColumns, SysTypes Where SysColumns.Id = object_Id (@Table_name) And SysColumns.Name = @Column_Name_Temp And SysColumns.XuserType = SysTypes.XuserType --- field formed syntax expressions Select @Column_Syntax_Temp = Case When @Column_Type_Temp In ( 'datetime' , 'image', 'int') Then @Column_Name_Temp '' @Column_Type_Temp When @Column_Type_Temp In ( 'binary', 'bit', 'char', 'varchar') Then @Column_Name_Temp '' @Column_Type_Temp ' (' Convert (VARC har (10), @ Column_Length_Temp) ')' Else @Column_Name_Temp '' @Column_Type_Temp '(' Convert (varchar (10), @ Column_Xprec_Temp) ',' Convert (varchar (10), @ Column_Xscale_Temp ) ')' End select @column_syntax = @column_syntax @

Column_Syntax_Temp ',' End --- processing field list If @Temp_Pos> 0 Begin Select @Select_Command_Temp = Right (@ Select_Command_Temp, Len (@Select_Command_Temp) - @Temp_Pos) End Else Begin Select @Select_Command_Temp = '' End End - - Forming the correct field to create a syntax Select @Column_Syntax = Left (@ Column_Syntax) - 1) --- Modify the structure of the temporary table EXECUTE ('ALTER TABLE #test ADD' @ Column_Syntax) Execute ('ALter Table #test Drop Column A ') --- Insert SELECT Structure Set into Temporary Table INSERT INTO #test Execute (@select_command) - Create Cursor Set @Cursor_Return = Cursor Local Scroll Read_only for select * from #test - - Open the cursor open @cursor_return

▲ Use a cursor

/ Note: There are several in Select, fetch from @cursor_name @cust_id should declare several variables, and the order and type must be consistent. * / Declare @cursor_name cursor, @select_command varchar (8000), @cust_id varchar (20 ) select @select_command = 'select cust_id from so_cust'execute usp_createcursor @ select_command, @ cursor_name OUTPUTfetch from @cursor_name into @cust_idwhile @@ fetch_status = 0 begin fetch from @cursor_name into @cust_id endclose @cursor_namedeallocate cursor_name

Description: The above code is passed on the MSS SQL Server 7.0. Other databases can only modify the grabbing and its type of system table.