Function that receive table name as input variable to be used in CROSS APPLY

Question

I need to create a Db function that returns a list of GUID, but from different tables.
So I'm trying to create a db function, that get as input the name of the table, and return a list (table with only one field), but since I cannot use dynamic sql all I tried doesn't work.

How can I do that? Every db function I tried to create, doesn't even pass the "syntax" check, and now I'm stuck.

SQL Server does not support macro substitution. This would require Dynamic SQL. That said, if you have a limited number of tables there is an ugly hack using conditional UNION ALLs — John Cappelletti, Apr 30 '23 at 16:05
a function cannot do dynamic sql, but you can do a big IF @table = 'tabl1' select .. from tabl1 else if ... If you need full dynamic you can use a stored procedure — siggemannen, Apr 30 '23 at 17:04
It *sounds* like your design is somewhat lacking, if you have numerous tables all with the same schema being dropped and created and you need to determine a table a runtime, perhaps you need to look at normalising your data into a table with a "table name" type column — Stu, Apr 30 '23 at 17:49
To expand on John's suggeation above (and now deleted answer), what you *can* do is dynamically generate the function itself and include all the possible tables, and alter it based on some other event or even DDL trigger - likewise you could dynamically create a view that combines data from multiple tables and consume it via a function. — Stu, Apr 30 '23 at 17:57
@Stu I'm working with a product, I'm not working with a data model I can modify — ff8mania, Apr 30 '23 at 18:49

abolfazl sadeghi · Answer 1 · 2023-05-01T06:58:17.223

0

This is just a procedure and I specified the code field and all the values are returned without where The input must include the name of the table and the schema, separated by a comma I cannot use EXEC in functions, I had to use stored procedures


alter proc TestProc1-- '[dbo].[T1],[dbo].[T2]'      
       @ListTAble nvarchar(100)
as
    
drop table if exists #listvalues;drop table if exists #tables
create table #listvalues (datas uniqueidentifier)
select 
         '['+a.TABLE_SCHEMA+ '].' + '['+a.TABLE_NAME+ ']' as TAbleNAme
         ,ROW_NUMBER() over(order by TABLE_NAME) as rw  
into #tables         
from INFORMATION_SCHEMA.TABLES a
where '['+a.TABLE_SCHEMA+ '].' + '['+a.TABLE_NAME+ ']' in (select * from string_split(@ListTAble,',') )

declare @query varchar(max)=''
declare @Count int=(select count(*) from #tables)
declare @I int=1
while(@Count>=@I)
begin

   set @query=' insert into #listvalues (datas)  select Id from  ' + (select TAbleNAme from #tables where rw=@I)
   exec(@query)
   set @I=@I+1

end 
select  STRING_AGG(cast( datas as nvarchar(max)),',') from  #listvalues

create table T1(id uniqueidentifier,Name nvarchar(100))
create table T2(id uniqueidentifier,Name1 nvarchar(100))
create table T3(id uniqueidentifier,Name2 nvarchar(100))
insert into T1 (id,Name) values(NEWID(),'ff')
insert into T1 (id,Name) values(NEWID(),'ff')
insert into T1 (id,Name) values(NEWID(),'ff')
insert into T2 (id,Name1) values(NEWID(),'ff')
insert into T2 (id,Name1) values(NEWID(),'ff')
insert into T2 (id,Name1) values(NEWID(),'ff')
insert into T3 (id,Name2) values(NEWID(),'ff')
insert into T3 (id,Name2) values(NEWID(),'ff')
insert into T3 (id,Name2) values(NEWID(),'ff')
insert into T3 (id,Name2) values(NEWID(),'ff')

edited May 01 '23 at 06:58

answered Apr 30 '23 at 17:03

abolfazl sadeghi

2,277
2
12
20

`sp_MSforeachtable` would be an awful way to do this. Having a cursor go through *every* table, regardless of if needed, is going to be *slow*. Far better to build the query you need. – Thom A Apr 30 '23 at 19:12
thank you for your opinion,I edited my code,I usually try to don't use cursor and use my query but this once use sp_MSforeachtable – abolfazl sadeghi Apr 30 '23 at 19:34
I cannot use a SP inside a function – ff8mania Apr 30 '23 at 21:37
I am agree with you @Aaron Bertrand.I tried all the methods, but unfortunately I did not find a better way, because the name of the tables is dynamic – abolfazl sadeghi Apr 30 '23 at 22:30
@AaronBertrand the function is the only choise because I'm building a view, and function I can use in cross apply, SP I can't – ff8mania May 01 '23 at 15:05
@AaronBertrand my only way is the view, because the product I'm using, only works with view – ff8mania May 01 '23 at 15:17
There are other ways, but they are not suitable and good . One method: you write a job(run every 1 minute and hour ,...) that create a view from all the tables(select id from table1 unionall select select id from table2 .... ) and you filter view – abolfazl sadeghi May 01 '23 at 15:18
@AaronBertrand you are being a little bit naive. If what I'm asking is not possible, that's fine, I'll find another way, but is not that every time you cannot do something you "change product". At least not in the enterprise world – ff8mania May 01 '23 at 21:27
@AaronBertrand now you are taking this a little bit too much personal. My assumption was that I needed a db function. That's it. Store procedure and changing the product are not possible solution, are just technical way, not compatible with what I'm doing. I cannot change the product and that's it. thanks for trying – ff8mania May 01 '23 at 22:23
1

@ff8mania /shrug like I said, good luck. We can only say "not possible" so many times. – Aaron Bertrand May 01 '23 at 22:29
Thank you very much for your time(Aaron Bertrand) and also Thank you to both of you( Aaron Bertrand,ff8mania). The goal of me and sir Aaron Bertrand was only to help you(ff8mania). Good luck – abolfazl sadeghi May 01 '23 at 22:33

Function that receive table name as input variable to be used in CROSS APPLY

1 Answers1