多表查询取前几列

环境:有一张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 
/****** 对象:  StoredProcedure \[dbo\].\[getMyCollectList\_sp\]    脚本日期: 07/15/2010 08:46:24 ******/
SET ANSI\_NULLS ON GO SET QUOTED\_IDENTIFIER ON GO -- =============================================
-- Author:  dzd -- Create date: 2010-7-14 -- Description: 收藏库多表查询 -- =============================================
ALTER PROCEDURE \[dbo\].\[getMyCollectList\_sp\]
-- Add the parameters for the stored procedure here
@UserId nvarchar(50) AS --定义sql语句变量
declare @nsql nvarchar(2000) DECLARE @strOrder varchar(400); -- 排序类型 DECLARE @StrGetFields varchar(1000);
-- 需要返回的列
--定义临时表
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