我这么写是不是死循环了,怎么解CREATE proc myTest @intFormDataID int, @intFormLayoutID int
as set nocount offdeclare cursorProcNames cursor for SELECT DISTINCT CommonAttribute.*,ItemGroupDef.StudyID , FormLayout.FormDefID, FormLayout.FormTitleText, FormLayout.AlternateItemGroupID, FormLayout.AlternateItemGroupName, FormLayout.SchemaID, FormData.FormDataID, FormData.IsBlank, ItemGroupDef.Name From CommonAttribute, FormLayout, FormData,ItemDef,ItemGroupDef,AlternateItemGroup Where FormData.FormDefID = FormLayout.FormDefID and CommonAttribute.FormLayoutID = FormLayout.FormLayoutID and ItemDef.ItemDefID= CommonAttribute.TableFieldID and AlternateItemGroup.ItemGroupDefID=ItemGroupDef.ItemGroupDefID and AlternateItemGroup.AlternateItemGroupID=FormLayout.AlternateItemGroupID and CommonAttribute.Deleted='0' and FormLayout.FormLayoutID= @intFormLayoutID and FormData.FormDataID = @intFormDataID declare @vchProcName varchar(255) declare @SQLString varchar(2505)
OPEN cursorProcNames Fetch Next From cursorProcNames select @vchProcName =name from ItemGroupDef While @@FETCH_STATUS=0 begin select @SQLString=' select * from ' + @vchProcName exec (@SQLString) --Fetch Next From cursorProcNames Into @vchProcName END Close cursorProcNames DEALLOCATE cursorProcNames return GO
OPEN cursorProcNames Fetch Next From cursorProcNames Into @vchProcName好象不能把那么多数据字段只赋给一个变量吧。我很少使用游标的。呵呵~~具体也说不上来。你把错误贴上来,大家帮你看看吧。
@intFormDataID int,
@intFormLayoutID int
as
set nocount offdeclare cursorProcNames cursor for
SELECT DISTINCT CommonAttribute.*,ItemGroupDef.StudyID ,
FormLayout.FormDefID,
FormLayout.FormTitleText,
FormLayout.AlternateItemGroupID,
FormLayout.AlternateItemGroupName,
FormLayout.SchemaID,
FormData.FormDataID,
FormData.IsBlank,
ItemGroupDef.Name
From CommonAttribute, FormLayout, FormData,ItemDef,ItemGroupDef,AlternateItemGroup
Where FormData.FormDefID = FormLayout.FormDefID
and CommonAttribute.FormLayoutID = FormLayout.FormLayoutID
and ItemDef.ItemDefID= CommonAttribute.TableFieldID
and AlternateItemGroup.ItemGroupDefID=ItemGroupDef.ItemGroupDefID
and AlternateItemGroup.AlternateItemGroupID=FormLayout.AlternateItemGroupID
and CommonAttribute.Deleted='0'
and FormLayout.FormLayoutID= @intFormLayoutID
and FormData.FormDataID = @intFormDataID
declare @vchProcName varchar(255)
declare @SQLString varchar(2505)
OPEN cursorProcNames
Fetch Next From cursorProcNames
select @vchProcName =name from ItemGroupDef
While @@FETCH_STATUS=0
begin
select @SQLString=' select * from ' + @vchProcName
exec (@SQLString)
--Fetch Next From cursorProcNames Into @vchProcName
END
Close cursorProcNames
DEALLOCATE cursorProcNames
return
GO