环境:有一张Global_CollectInfo_tb来存放有关收藏的记录包括收藏者ID,收藏的记录的主表ID,收藏的记录在具体副表里的ID,收藏记录在主表里的ID,具体的数据记录放在各个副表里. Global_TableDetail_tb里存放有关表的具体信息,包括列名,列类型,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| USE \[cys\] GO SET ANSI\_NULLS ON GO SET QUOTED\_IDENTIFIER ON GO
ALTER PROCEDURE \[dbo\].\[getMyCollectList\_sp\]
@UserId nvarchar(50) AS declare @nsql nvarchar(2000) DECLARE @strOrder varchar(400);
create TABLE #t(\[id\] nvarchar(50),\[字段1\] nvarchar(500),\[字段2\] nvarchar(500),\[字段3\] nvarchar(500),\[字段4\] nvarchar(500),\[字段5\] nvarchar(500),\[来源表\] nvarchar(500))
DECLARE tnames\_cursor CURSOR FOR SELECT Global\_TableDetail\_tb.TableName,Global\_TableDetail\_tb.TableDetailId FROM Global\_TableDetail\_tb INNER JOIN Global\_CollectInfo\_tb ON Global\_TableDetail\_tb.TableDetailId = Global\_CollectInfo\_tb.CollectTableId WHERE (Global\_CollectInfo\_tb.CollectorId = @UserId) and Global\_TableDetail\_tb.IsShow='true' group by Global\_TableDetail\_tb.TableName,Global\_TableDetail\_tb.TableDetailId OPEN tnames\_cursor declare @TableName nvarchar(100),@TableDetailId nvarchar(100) FETCH next from tnames\_cursor into @TableName,@TableDetailId while @@fetch\_status=0 begin select top 5 @StrGetFields=isnull(@StrGetFields+',','')+case when ColumnType ='date' then 'convert(nvarchar(100), ' else '' end + ColumnName+ case when ColumnType ='date' then ',120)' else '' end from Global\_ColumnConfiger\_tb where [TableId=@TableDetailId](TableId=@TableDetailId) order by ColumnIndex asc SET @nsql = 'SELECT id,' [+@StrGetFields](mailto:+@StrGetFields) +',TableName FROM Global\_TableDetail\_tb INNER JOIN Global\_CollectInfo\_tb ON Global\_TableDetail\_tb.TableDetailId = Global\_CollectInfo\_tb.CollectTableId INNER JOIN ' + @TableName + '_副表 ON Global\_CollectInfo\_tb.CollectRecordId = '+ @TableName+'_副表.id WHERE (Global\_CollectInfo\_tb.CollectorId = ['''+@UserId+''']('''+@UserId+''')) AND (Global\_TableDetail\_tb.IsShow = ''true'')' print(@nsql) insert into #t execute(@nsql) set @StrGetFields = null FETCH next from tnames\_cursor into @TableName,@TableDetailId end CLOSE tnames\_cursor DEALLOCATE tnames_cursor select * from #t
|