使用动态SQL返回TABLE的T-SQL函数

EDIT: Some of those who would offer help are unclear about the nature of the requirement, so I will try to state it as clearly as I can:

编辑：一些提供帮助的人不清楚要求的性质，所以我会尽可能清楚地说明：

We need to instantiate a view of an underlying table, and this view must be able to be joined to another table; the difficulty is that the identity of the underlying table is not known until runtime of the ad hoc query doing the join. We would like to do something like this:

我们需要实例化一个基础表的视图，并且该视图必须能够连接到另一个表;困难在于，在进行连接的即席查询的运行时，才知道基础表的标识。我们想做这样的事情：

  select * from foo
   inner join dynamicallyInstantiatedTable(condition) DT
   on foo.zipcode = DT.zipcode

It doesn't seem possible to create a function that returns TABLE if the function uses dynamic SQL. This is not valid:

如果函数使用动态SQL，则似乎无法创建返回TABLE的函数。这是无效的：

   declare @tablename varchar(50);
   -- <snip> code to determine the name of @tablename

   declare @statement varchar(1000);
   set @statement = 'select * from ' + @tablename;
   exec( @statement);

The error:

错误：

Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.

在函数中无效使用副作用运算符'EXECUTE STRING'。

If the table name is not known beforehand for whatever reason (e.g. tables are constantly being added and we must select against the most recent one, say), is it possible to do the select dynamically and return a table, either in a stored proc or function?

如果由于某种原因事先不知道表名（例如，表是经常添加的，我们必须选择最新的表，比如说），是否可以动态地执行select并返回表，无论是在存储过程中还是功能？

4 个解决方案

#1

Dynamic SQL in function. No.

动态SQL的功能。没有。

is it possible to do the select dynamically and return a table, either in a stored proc or function?

是否有可能动态地选择并返回一个表，在存储过程或函数中？

Perhaps I'm missing something (would not be a first) but this seems simple as a stored proc:

也许我错过了一些东西（不会是第一个），但这似乎是一个简单的存储过程：

The Proc

Proc

create proc dbo.getRowsFrom @tablename varchar(50) as
exec('select * from ' + @tablename);

Use

使用

exec dbo.getRowsFrom '<my table>';

Is that what you're looking for?

这就是你要找的东西吗？

#2

you should once explain your requirement with example.It is not clear to anybody.

你应该用例子来解释你的要求。任何人都不清楚。

I think everything thing can be done within single proc,no need of another proc or UDF.

我认为一切都可以在单个proc中完成，不需要其他proc或UDF。

declare @tblname varchar(500)
select  @tblname=name from sys.objects
where type_desc ='USER_TABLE'
order by create_date DESC

declare @Sql varchar(max)=''
set @Sql='select * into #tmp from '+@tblname+'  '

set @Sql=@Sql+' select *  from #tmp   drop table #tmp'
exec (@Sql)

#3

Little detail given about your 'join' situation, but it might be easier to jump to your final joined results, rather than focusing on the input table in isolation.

关于你的“加入”情况的细节很少，但跳转到最终的联合结果可能更容易，而不是孤立地关注输入表。

Here I am joining my input table 'a' to a lookup table 'ref', and outputting joined results. If tomorrow your have another input table 'b' - this proc will join that to the lookup table instead. The only requirement is that the join column is consistent.

在这里，我将输入表'a'加入查找表'ref'，并输出连接结果。如果明天你有另一个输入表'b' - 这个proc将把它连接到查找表。唯一的要求是连接列是一致的。

  declare 
  @inputTableName nvarchar(128)
  ,@sqlExec nvarchar(max)

  set @inputTableName = 'b';

  if(not exists (select 1 from INFORMATION_SCHEMA.TABLES where table_schema = 'test' and TABLE_NAME = 'myView'))
  begin
        select @sqlExec = 'create view test.myView as 
                            select I.*,R.[text] from test.[' + @inputTableName + '] I inner join test.ref  R on I.col0 = R.col0'
end else begin
        select @sqlExec = 'alter view test.myView as 
                            select I.*,R.[text] from test.[' + @inputTableName + '] I inner join test.ref  R on I.col0 = R.col0'
end
    exec (@sqlExec)

select * from test.myView

#4

Here we go.

开始了。

I don't use synonyms often, but CREATE SYNONYM supports dynamic SQL.

我不经常使用同义词，但CREATE SYNONYM支持动态SQL。

declare @tablename nvarchar(128);
-- <some code to set @tablename>

declare @sql nvarchar(500);

if object_id(N'dbo.TodaysData', N'SN') is not null
    drop synonym dbo.TodaysData;

set @sql = 
    'create synonym dbo.TodaysData
     for ' + @tablename;

execute(@sql);

select top 5 
    *
from 
    dbo.TodaysData as t
    join
    dbo.SomeOtherTable as s
        on 
           s.FieldName = t.HeresHopingYourSchemaDoesntChange

#1