Create | dynamic | cursors
▲ Create Cursors
CREATE PROCEDURE usp_createcursor (@Select_Command varchar (8000), @Cursor_Return Cursor varying OUTPUT) as
/* Stored Procedure name: Usp_createcursor
Function Description: Creates a dynamic cursor based on the selected select
Parameter description: @Select_Command---Select statement; @Cursor_Return---the cursor variable to return
Idea: The key to a dynamic cursor is not knowing how to construct its SELECT statement, because a select is a string table quantity that cannot be used directly when defined, but it can come from a table.
So my goal is to create a unified table, from which to take the data. The table has a certain grammatical rules, so it should be based on the column list to generate the corresponding
Format, which can be obtained from the system table. The key question is how to insert the data into the temp table, and I figured out a statement to
To implement this feature, insert into <TABLE_NAME> EXECUTE <sql>, while SQL7.0 help is not spoken. You can create a table with data.
Create Person: Kangjianmin
Date Created: 2001-07-11
*/
Declare @Select_Command_Temp Varchar (8000),---store Select temporary syntax
@Table_List varchar (255),---List of stored tables
@Column_List varchar (8000),---Storage column list
@Table_Name varchar,---A separate table name
@Column_Name varchar (),---hold a separate field name (but it may be *)
@Column_Syntax varchar (8000),---syntax for storing field tables (integrated)
@Column_Name_Temp varchar,---Storage field Name
@Column_Type_Temp varchar,----storage field type
@Column_Syntax_Temp varchar (8000),---The Syntax (single) of the table when the field is stored
@Column_Length_Temp int,---Storage field length
@Column_Xprec_Temp int,---storing field precision
@Column_Xscale_Temp int,---store field decimal places
@From_Pos int,---where to store from
@Where_Pos int,---where to store
@Having_Pos int,---Store a having position
@Groupby_Pos int,---where to store Groupby
@Orderby_Pos int,---where to store by
@Temp_Pos int,---Temporary variable
@Column_Count int,---Store The total number of fields
@Loop_Seq int---loop step variable
---create a temporary table
Create Table #Test (a int)
---If the SELECT statement is not started with ' select ', automatically modifies
If Left (Lower (Ltrim @Select_Command), 6) <> ' SELECT ' Select @Select_Command = ' SELECT ' + @Select_Command
---to remove the opening ' SELECT '
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 each reserved word position to get 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 (' by ', @Select_Command_Temp)
If @Where_Pos > 0 Select @Temp_Pos = @Where_Pos
If @Having_Pos > 0 and @Having_Pos < @Temp_Pos Select @Temp_Pos = @Having_Pos
If @Groupby_Pos > 0 and @Groupby_Pos < @Temp_Pos Select @Temp_Pos = @Groupby_Pos
If @Orderby_Pos > 0 and @Orderby_Pos < @Temp_Pos Select @Temp_Pos = @Orderby_Pos
---fetch table list
If @Temp_Pos > 0
Begin
Select @Table_List = SUBSTRING (@Select_Command_Temp, @From_Pos + 6, @Temp_Pos-@From_Pos-1)
End
Else
Begin
Select @Table_List = SUBSTRING (@Select_Command_Temp, @From_Pos + 6, Len (@Select_Command_Temp)-@From_Pos-1)
End
Select @Column_Syntax = '
---Only list the place of the slaughter
Select @Select_Command_Temp = Left (@Select_Command_Temp, @From_Pos-1)
While Len (@Select_Command_Temp) > 0
Begin
---take a comma position
Select @Temp_Pos = CHARINDEX (', ', @Select_Command_Temp)
---first fetch field name
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
---fetch table name and field name (possibly ' * ')
If CHARINDEX ('. ', @Column_Name) > 0
Begin
Select @Table_Name = Left (@Column_Name, CHARINDEX ('. ', @Column_Name)-1)
Select @Column_Name = Right (@Column_Name, Len (@Column_Name)-CHARINDEX ('. ', @Column_Name))
End
Else
Begin
Select @Table_Name = @Table_List
End
---field appears ' * '
If CHARINDEX (' * ', @Column_Name) > 0
Begin
Select @Column_Name = '
Select @Loop_Seq = 1
---Number of fields to take
Select @Column_Count = Count (*)
From syscolumns
Where Id = object_id (@Table_name)
While @Loop_Seq <= @Column_Count
Begin
---Take the field name, field type, length, precision, decimal place
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
---form a field-syntax expression
Select @Column_Syntax_Temp = case when @Column_Type_Temp in (' DateTime ', ' image ', ' int ') Then @Column_Name_Temp + ' + @Col Umn_type_temp
When @Column_Type_Temp in (' binary ', ' bit ', ' char ', ' varchar ') Then @Column_Name_Temp + ' + @Column_Type_Temp + ' (' +convert (Varchar, @Column_Length_Temp) + ') '
Else @Column_Name_Temp + ' + @Column_Type_Temp + ' (' +convert (varchar, @Column_Xprec_Temp) + ', ' + Convert (varchar (10) , @Column_Xscale_Temp) + ') '
End
Select @Column_Syntax = @Column_Syntax + @Column_Syntax_Temp + ', '
Select @Loop_Seq = @loop_Seq + 1
End
End
Else
Begin
---take a field name
Select @Column_Name_Temp = @Column_Name
---take field type, length, precision, decimal place
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
---form a field-syntax expression
Select @Column_Syntax_Temp = case when @Column_Type_Temp in (' DateTime ', ' image ', ' int ') Then @Column_Name_Temp + ' + @Col Umn_type_temp
When @Column_Type_Temp in (' binary ', ' bit ', ' char ', ' varchar ') Then @Column_Name_Temp + ' + @Column_Type_Temp + ' (' +convert (Varchar, @Column_Length_Temp) + ') '
Else @Column_Name_Temp + ' + @Column_Type_Temp + ' (' +convert (varchar, @Column_Xprec_Temp) + ', ' + Convert (varchar (10) , @Column_Xscale_Temp) + ') '
End
Select @Column_Syntax = @Column_Syntax + @Column_Syntax_Temp + ', '
End
---processing column 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
---form the correct field creation syntax
Select @Column_Syntax = Left (@Column_Syntax, Len (@Column_Syntax)-1)
---Modify the structure of a temporary table
Execute (' Alter Table #Test Add ' + @Column_Syntax)
Execute (' Alter Table #Test Drop Column a ')
---Inserts a set of the select execution into a temporary table
Insert into #Test
Execute (@Select_Command)
---create a cursor
Set @Cursor_Return = Cursor local SCROLL read_only for
Select *
From #Test
---open cursors
Open @Cursor_Return
▲ Using Cursors
/NOTE: There are several items in the Select, the fetch from @cursor_name into @cust_id should declare several variables, and the order and type must be identical. * *
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 OUTPUT
Fetch from @cursor_name into @cust_id
While @ @fetch_status = 0
Begin
Fetch from @cursor_name into @cust_id
End
Close @cursor_name
DEALLOCATE cursor_name
Description: The above code is passed on the MSS SQL SERVER7.0. Other databases just need to modify the crawl fields and their type of system tables.
