多张表，把表名当参数查询所有表的数据

多张类似的表，有共同列，求去多表的汇总查询到DataTable中 ，此方法使用游标查询表的集合，利用游标循环将Tablename值付给要查询的SQL查询出数据。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[proc_getalltable]
(
@Deptid varchar(20),
@uid int,
@start datetime,
@end datetime
)
AS
Create table #gettemp
(
id int,
uid int,
col float,
ScoreStatus int,
fillingtime datetime
)
BEGIN
 SET NOCOUNT ON;

    DECLARE @TypeScoreId int,@tablename VARCHAR(100),@Sql varchar(2000)           

    declare tablename cursor for  select ScoreId,DataBaseDBO  from info_TypeScore
                                  where substring(ScoreNum,0,3)=@Deptid and ScoreStatus=1

    open tablename
    fetch next from tablename into @TypeScoreId,@tablename
 

    while @@FETCH_STATUS = 0
    begin
       set @Sql =N'select id,uid,col,ScoreStatus,fillingtime from ' + @tablename
             +' where TypeScoreId='+cast(@TypeScoreId as nvarchar(20))
             +' and uid='+cast(@uid as nvarchar(20))
    +' and fillingtime between convert(datetime,'''+cast(@start as nvarchar(20))+''')'
             +' and convert(datetime,'''+cast(@end as nvarchar(20))+''')'
        insert into #gettemp  
        exec(@Sql)
        fetch next from tablename into @TypeScoreId,@tablename
    end               

    close tablename
    deallocate tablename
 select * from #gettemp
end

此方法我已经应用过，如果有问题可以给我留言。。。。。