Function Data from (Multiple?) Tables

[Sedanta]

Hi Forum,
I need a funtion to retrieve data from more then one Table. The tables are not related.Field names are the same in all tables. The table list I get from a view and i need to go through this list and pull out any records from the different tables that have a true value in a certain field. I am new to SQL and would appreciate any help. I am using SQL 2000

Thanks
Sedanda

[Sedanta]

My Sp retrieves all the names of the tables that I need
I would like to use my SP dynamically in a loop with a union select command

GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @Table varchar(50);

DECLARE Table_cursor CURSOR FOR
SELECT Projekt FROM C_TFLS_Status_Matrix
WHERE FLSExportAktiv = -1
ORDER BY Projekt;

OPEN Table_cursor;

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM Table_cursor
INTO @Table;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
   PRINT @Table

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM Table_cursor
   INTO @Table;
END

CLOSE Table_cursor;
DEALLOCATE Table_cursor;
GO

But the problem is that I dont know where to start.

Has anyone an idea how to do this? and please explait it for beginners.

[NeoPa]

I struggled to find anything that could work for this, but my T-SQL experience is admittedly limited. Someone may well come up with a simpler solution.

My approach would be to create a Table-Valued User-Defined Function. It would need to take the list of table names as a parameter, and would return the data for the first table as well as checking if any further tables are left in the list and, if so, calling itself recursively to append the rest.

I'm sorry I'm not in a position to make this easy enough for a beginner, but that's a big ask considering the time it would require. Have a play and see what you come up with. We may be able to help further down the line if you have a specific problem.

[ck9663]

How many tables are there in total? Do you know the names of these tables? How many tables are you expecting that the view give you as list of table names that you need to read?

~~ CK

[Greg Stowell]

You mentioned all of the tables contain the same column names....

Do all of the tables have the same data types, same number of columns (with the columns ordinal positions all the same), and you're just looking to create something to do dynamic SQL to union them all together?

[Sedanta]

Hi Greg
That is correct I need a function to where i Give the list of table names as a Parameter.

[ck9663]

There could be other ways of doing it than a function. Like a conditional where, perhaps. It all depends on how many the tables are, the condition that you need to test, the expected size of the result set, etc...

You can even return a result set (table-like) parameter from a stored procedure.

Good Luck!!!

~~ CK

[Greg Stowell]

This might be what you're looking for - its in a stored proc format - but you could switch it around to be a function without much trouble...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.[sp_UnionEm]

AS

BEGIN

DECLARE @tablenames varchar(8000)
DECLARE @query varchar(8000)

DECLARE aCursor Cursor for

SELECT 
	tablename
FROM 
	Tablelist

OPEN aCursor
Fetch aCursor into @tablenames

SET @query = 'SELECT * FROM [' + @tablenames + '] '

Fetch aCursor into @tablenames

WHILE @@Fetch_Status = 0
BEGIN
	SET @query = @query + 'union all SELECT * FROM [' + @tablenames + '] '
	Fetch aCursor into @tablenames
END

exec (@query)


CLOSE aCursor
DEALLOCATE aCursor

END

[Greg Stowell]

I forgot - Notice the varchar(8000) on the @query?
Be careful how many tables you pull in.. go over that 8000 chars, and it gets truncated.

Good luck, and let me know if the concept helps, or if you get any errors, I can help debug (kinda busy at work - so my replies may take some time).