T-SQL function that returns TABLE using dynamic SQL

Question

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:

   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.

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?

Can the SP return the table so that it could be treated as a table, that is, the stored proc name acts as a tablename in another select statement? select * from myproc inner join T.... — Tim
– Tim, Commented Oct 9, 2017 at 19:00
@Tim Ah, I see what you're asking. Unfortunately, no, you can't do that in either a stored procedure, view, or function. The closest you can get is a procedure that returns a resultset, but you can't treat it as a table. — Siyual
– Siyual, Commented Oct 9, 2017 at 19:01
Some really ugly way SELECT * FROM OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters') combined with dynamic SQL may work, But still I wouldn't go that way — Lukasz Szozda
– Lukasz Szozda, Commented Oct 9, 2017 at 19:04
tables are constantly being added and we must select against the most recent one It indicates table per date, which is IMHO poor design. And it is antipattern SELECT * FROM sales + @yymm — Lukasz Szozda
– Lukasz Szozda, Commented Oct 9, 2017 at 19:07

Eric Brandt · Accepted Answer · 2017-10-10 21:44:35Z

2

Here we go.

I don't use synonyms often, but CREATE SYNONYM supports dynamic 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

answered Oct 10, 2017 at 21:44

Eric Brandt

8,1313 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

str8ball Over a year ago

Very creative! I like the dynamic use of the synonym.

Alan Burstein · Accepted Answer · 2017-10-09 20:57:31Z

0

Dynamic SQL in function. No.

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

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?

answered Oct 9, 2017 at 20:57

Alan Burstein

8,0061 gold badge18 silver badges19 bronze badges

2 Comments

Tim Over a year ago

I need to join <my table> to another.

Alan Burstein Over a year ago

Ah - ok. Makes sense. Stored proc is out too then. The OPENQUERY solution that lad2025 posted may be the way to go.

KumarHarsh · Accepted Answer · 2017-10-10 05:57:29Z

0

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.

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)

answered Oct 10, 2017 at 5:57

KumarHarsh

5,0941 gold badge22 silver badges25 bronze badges

Comments

SimonB · Accepted Answer · 2017-10-10 11:04:25Z

0

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.

  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

answered Oct 10, 2017 at 11:04

SimonB

1,0652 gold badges24 silver badges48 bronze badges

1 Comment

Tim Over a year ago

Thanks for this suggestion, Simon. I have to run but will check later and circle back.

Collectives™ on Stack Overflow

T-SQL function that returns TABLE using dynamic SQL

4 Answers 4

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related