If@Where_Pos>0Select@Temp_Pos=@Where_Pos
If@Having_Pos>0And@Having_Pos<@Temp_PosSelect@Temp_Pos=@Having_Pos
If@Groupby_Pos>0And@Groupby_Pos<@Temp_PosSelect@Temp_Pos=@Groupby_Pos
If@Orderby_Pos>0And@Orderby_Pos<@Temp_PosSelect@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)
WhileLen(@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
---取表名和栏位名（多是‘*’）
IfCHARINDEX(.,@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
---栏位呈现*
IfCHARINDEX(*,@Column_Name)>0
Begin
Select@Column_Name=
Select@Loop_Seq=1
---取栏位个数
Select@Column_Count=Count(*)
FromSysColumns
WhereId=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
FromSysColumns,SysTypes
WhereSysColumns.Id=Object_Id(@Table_name)And
SysColumns.Colid=@Loop_SeqAnd
SysColumns.XuserType=SysTypes.XuserType
---构成栏位语法表达式
Select@Column_Syntax_Temp=CaseWhen@Column_Type_TempIn(datetime,image,int)Then@Column_Name_Temp++@Column_Type_Temp
When@Column_Type_TempIn(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)
FromSysColumns,SysTypes
WhereSysColumns.Id=Object_Id(@Table_name)And
SysColumns.Name=@Column_Name_TempAnd
SysColumns.XuserType=SysTypes.XuserType
---构成栏位语法表达式
Select@Column_Syntax_Temp=CaseWhen@Column_Type_TempIn(datetime,image,int)Then@Column_Name_Temp++@Column_Type_Temp
When@Column_Type_TempIn(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(AlterTable#TestAdd+@Column_Syntax)
Execute(AlterTable#TestDropColumna)
---将SELECT实行的布局集拔出光临时表
InsertInto#Test
Execute(@Select_Command)
---创立游标
Set@Cursor_Return=CURSORLOCALSCROLLREAD_ONLYFOR
Select*
From#Test
---翻开游标
Open@Cursor_Return
▲利用游标
/注:在SELECT中有几项,fetchfrom@cursor_nameinto@cust_id就应当声明几个变量,并且按次和范例必需分歧.*/
declare@cursor_namecursor,
@select_commandvarchar(8000),
@cust_idvarchar(20)
select@select_command=selectcust_idfromso_cust
executeusp_createcursor@select_command,@cursor_nameOUTPUT
fetchfrom@cursor_nameinto@cust_id
while@@fetch_status=0
begin
fetchfrom@cursor_nameinto@cust_id
end
close@cursor_name
deallocatecursor_name