SQL Server - List of parameters into function, Export Multiple results into Excel

Question

I have a function that takes an ID as an input. The function gets all related IDs from the ID you input. When I run the function five times with different ID numbers such as:

select * from [levelfunction](100)
select * from [levelfunction](101)
select * from [levelfunction](102)
select * from [levelfunction](104)
select * from [levelfunction](108)

CREATE TABLE #Table1(
    ID [int] NULL,
    Level [int] NULL)

    INSERT INTO #Table1 (ID, Level) VALUES (100,0)

CREATE TABLE #Table2(
    ID [int] NULL,
    Level [int] NULL)

    INSERT INTO #Table2 (ID, Level) VALUES (101,0)

CREATE TABLE #Table3(
    ID [int] NULL,
    Level [int] NULL)

    INSERT INTO #Table3 (ID, Level) VALUES (102,0)

CREATE TABLE #Table4(
    ID [int] NULL,
    Level [int] NULL)

    INSERT INTO #Table4 (ID, Level) VALUES (103,0), (104,1), (105,2), (106,3)

CREATE TABLE #Table5(
    ID [int] NULL,
    Level [int] NULL)

    INSERT INTO #Table5 (ID, Level) VALUES (107,0), (108,1), (109,2)

I get the below result:

   select * from #Table1
   select * from #Table2
   select * from #Table3
   select * from #Table4
   select * from #Table5
+-----+-------+
| ID  | Level | 
+-----+-------+
| 100 |  0    | 
+-----+-------+
+-----+-------+
| ID  | Level | 
+-----+-------+
| 101 |  0    |
+-----+-------+
+-----+-------+
| ID  | Level | 
+-----+-------+    
| 102 |  0    |
+-----+-------+
+-----+-------+
| ID  | Level | 
+-----+-------+     
| 103 |  0    |
| 104 |  1    |
| 105 |  2    |
| 106 |  3    |
+-----+-------+ 
+-----+-------+
| ID  | Level | 
+-----+-------+ 
| 107 |  0    |
| 108 |  1    |
| 109 |  2    |
+-----+-------+

How do I -

-Put a list of IDs into a function without altering the function?

-Export all the tables into excel as 1 result?

Expected excel result:

+-----+-------+
| ID  | Level | 
+-----+-------+
| 100 |  0    | 
| 101 |  0    |
| 102 |  0    |
| 103 |  0    |
| 104 |  1    |
| 105 |  2    |
| 106 |  3    |
| 107 |  0    |
| 108 |  1    |
| 109 |  2    |
+-----+-------+

score 1 · Accepted Answer · edited May 23 '17 at 12:14

For

Put a list of IDs into a function without altering the function?

use CROSS APPLY

declare @IDlist table(ID int)
insert into @IDlist(ID) values (100),(101),(102),(103),(104),(105),(106),(107)

select a.Id,a.Level from @IDlist d
cross apply levelfunction(d.ID) as a;

About export to excel was answered more then one time, for example here OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Bookmark is invalid."

SQL Server - List of parameters into function, Export Multiple results into Excel

1 Answers1