SQL SREVER如何创建和使用动态游标

核心提示：创建游标 CREATE PROCEDURE Usp_CreateCursor(@Select_Command varchar(8000),@Cursor_Return CURSOR VARYING OUTPUT) AS/*存储过程名称：Usp_CreateCursor功能描述：根据指定的SELECT创建一个动态游标参数

创建游标

CREATE PROCEDURE Usp_CreateCursor(@Select_Command varchar(8000),@Cursor_Return CURSOR VARYING OUTPUT) AS
/*存储过程名称：Usp_CreateCursor
　功能描述：　　根据指定的SELECT创建一个动态游标
　参数描述：　　@Select_Command ---SELECT语句;@Cursor_Return ---要返回的游标变量
　思路：　　　　动态游标的关键是不知如何去构造它的SELECT语句，因为SELECT是个字符串表量，定义时不能直接用它，但它可以来源于表。
　　　　　　　　所以我的目的就是创建一个统一的表，从中取数据不就可以了。建表有一定的语法规则，所以就应该根据栏位列表生成相应的
　　　　　　　　格式，这个可以从系统表中获取。关键的问题是如何将数据插入到临时表，我摸索出一条语句可
　　　　　　　　实现这个功能，那就是INSERT INTO <TABLE_NAME> EXECUTE <SQL>，而SQL7.0的帮助未讲。有表有数据就可以创建了。
　创建人：　　　康剑民
　创建日期：　　2001-07-11
*/
Declare @Select_Command_Temp Varchar(8000), ---存放SELECT临时语法
　　　　@Table_List varchar(255), ---存放表的列表
　　　　@Column_List varchar(8000),---存放栏位列表
　　　　@Table_Name varchar(30),---存放单独表名
　　　　@Column_Name varchar(30),---存放单独栏位名（但有可能是*）
　　　　@Column_Syntax varchar(8000),---存放栏位建表时的语法（综合）
　　　　@Column_Name_Temp varchar(30),---存放栏位名称
　　　　@Column_Type_Temp varchar(30),----存放栏位类型
　　　　@Column_Syntax_Temp varchar(8000),---存放栏位建表时的语法（单个）
　　　　@Column_Length_Temp int,---存放栏位长度
　　　　@Column_Xprec_Temp int,---存放栏位精度
　　　　@Column_Xscale_Temp int,---存放栏位小数位数
　　　　@From_Pos int,---存放from的位置
　　　　@Where_Pos int,---存放where的位置
　　　　@Having_Pos int,---存放having的位置
　　　　@GrouPBy_Pos int,---存放groupby的位置
　　　　@Orderby_Pos int,---存放orderby的位置
　　　　@Temp_Pos int,---临时变量
　　　　@Column_Count int,---存放栏位总数
　　　　@Loop_Seq int---循环步进变量
　　
---创建临时表
Create Table #Test(a int)
---如果传来的SELECT语句不是以'select'开头，自动修改
If Left(Lower(Ltrim(@Select_Command)),6) <> 'select' Select @Select_Command = 'Select ' + @Select_Command
---将开头‘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)
---取各保留字位置，以便获得表的列表
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_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
---取表列表
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 = ''
---只列出栏位
Select @Select_Command_Temp = Left(@Select_Command_Temp,@From_Pos - 1)
While Len(@Select_Command_Temp) > 0
　 Begin
　 ---取逗号位置
　 Select @Temp_Pos = CHARINDEX(',',@Select_Command_Temp)
　 ---初次取栏位名称
　 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
　 ---取表名和栏位名（可能是‘*’）
　 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
　　
　 ---栏位出现'*'
　 If CHARINDEX('*',@Column_Name) > 0
　　　Begin
　　　Select @Column_Name = ''
　　　Select @Loop_Seq = 1
　　　---取栏位个数
　　　Select @Column_Count = Count(*)
　　　　From SysColumns
　　　 Where Id = Object_Id(@Table_name)
　　　While @Loop_Seq <= @Column_Count
　　　　 Begin
　　　　 ---取栏位名称,栏位类型，长度，精度，小数位
　　　　 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
　　　　 ---形成栏位语法表达式
　　　　 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(Varchar(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 + ','
　　　　 Select @Loop_Seq = @loop_Seq + 1
　　　　 End
　　　End
　 Else
　　　Begin
　　　---取栏位名称
　　　Select @Column_Name_Temp = @Column_Name
　　　---取栏位类型，长度，精度，小数位
　　　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
　　　---形成栏位语法表达式
　　　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(Varchar(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
　　　---处理栏位列表
　　　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
　 ---形成正确的栏位创建语法
　 Select @Column_Syntax = Left(@Column_Syntax,Len(@Column_Syntax) - 1)
　 ---修改临时表的结构
　 Execute('Alter Table #Test Add '+@Column_Syntax)
　 Execute('Alter Table #Test Drop Column a')
　 ---将SELECT执行的结构集插入到临时表
　 Insert Into #Test
　 Execute(@Select_Command)
　 ---创建游标
　 Set @Cursor_Return =　CURSOR LOCAL SCROLL READ_ONLY FOR
　　　　　　　　　　　　 Select *
　　　　　　　　　　　　　 From #Test　　　　
　 ---打开游标　　　　　　　　
　 Open @Cursor_Return