Union of two tables on different databases on same SQL server (UDF)

Hello,

I am using a UDF and have a SELECT to union values from two tables that exist on the same SQL Server, but on a different database.

For example...
(
  SELECT Credit
  FROM [Company2003].[dbo],[Transactions]
)
UNION
(
  SELECT Credit
  FROM [Company2004].[dbo],[Transactions]
)

My Problem is, that I would like to pass the table names ([Company2003].[dbo],[Transactions])
as parameters so that I can execute my function for different Database Names.


Any ideas anyone?
Thanx
lil_gustaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

edwardsearchCommented:
Try This


ALTER PROCEDURE test
@dbname1 varchar(20),
@dbname2 varchar(20)
AS

SET QUOTED_IDENTIFIER OFF
DECLARE @lc_SQL nvarchar(100)
SET @lc_SQL = "SELECT * FROM " + @dbname1 + ".dbo.customers UNION ALL SELECT * FROM " + @dbname2 + ".dbo.customers"
EXEC (@lc_SQL)

exec test 'northwind'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bukkoCommented:

That would work, but the only drawbacks would be that you would need select permissions on the database tables because of the dynamic SQL string, and the SQL needs a recompile with every call.
How many databases/tables are there?
If there aren't too many, code each table explicitly. Select data into a temp table then select it all, e.g.:

CREATE TABLE #temp_table ( id int, name nvarchar(32) ) -- (need to add correct fields)
     
IF @dbtable = '[Company2003].[dbo],[Transactions]'
    BEGIN
    INSERT #TEMP
    SELECT [Company2003].[dbo],[Transactions].*
    FROM [Company2003].[dbo],[Transactions]
    END
IF @dbtable = '[Company2004].[dbo],[Transactions]'
    BEGIN
    INSERT #TEMP
    SELECT [Company2004].[dbo],[Transactions].*
    FROM [Company2004].[dbo],[Transactions]
    END
IF @dbtable = '[Company2005].[dbo],[Transactions]'
    BEGIN
    INSERT #TEMP
    SELECT [Company2005].[dbo],[Transactions].*
    FROM [Company2005].[dbo],[Transactions]
    END

SELECT #TEMP.*FROM #TEMP
DROP TABLE #TEMP

This way you don't need select permission on the tables, just database access and execute permissions on the stored proc.
You could also just pass the year, or a numeric reference, instead of the full table name.

Regards

bukko

0
rehandCommented:


Create Procedure SomeProcedure(
@Table1 VARCHAR(255),
@Table2 VARCHAR(255)
) AS

Exec( 'SELECT Credit FROM [Company2003].[dbo].'  + @Table1 + 'UNION ' +
         'SELECT Credit FROM [Company2004].[dbo].' + @Table2 )
Go
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lil_gustaAuthor Commented:
The Databases could become too many in years to come...
The Company's Financial Year information (Customers, Transactions etc.) is kept on
a different Database created dynamically...
  CompanyName2003, CompanyName2004 and so on.
I cannot predict how many Databases there will be created but I need to union the current one
with the previous one so as to create some report. I can determine which DB names
should be used, but the call to my Function should be using the DB names dynamically.

Bare in mind that all this has to be done within a FUNCTION, not a stored procedure
because of the complexity of what is being calculated within the function (no need to get into
detail).



Therefore, I need something like this...

DECLARE @DBandTableName1 VARCHAR(255)
DECLARE @DBandTableName2 VARCHAR(255)

SET @DBandTableName1 = '[Company2003].[dbo].[Transactions]'
SET @DBandTableName1 = '[Company2004].[dbo].[Transactions]'

SELECT * FROM @DBandTableName1
UNION
SELECT * FROM @DBandTableName2

(Perhaps this cannot be done server-side... I can obviously do it on the client /Delphi/ but I would
 rather do the job on the server)

Thanx :)
0
rehandCommented:
Ok I misunderstood. here is the fix

Create Procedure SomeProcedure(
@Table1 VARCHAR(255),
@Table2 VARCHAR(255)
) AS

Exec( 'SELECT Credit FROM '  + @Table1 + 'UNION ' +
         'SELECT Credit FROM ' + @Table2 )
Go



You can call it like:

EXEC SomeProcedure ('[Company2003].[dbo].[Transactions]', '[Company2004.[dbo].[Transactions]'

0
bukkoCommented:

...except that a function will only return a value, not a results set.

bukko
0
Anthony PerkinsCommented:
No to mention the fact that Exec is "verbotten" in UDF's.
0
rehandCommented:

Create Procedure SomeProcedure(
@Table1 VARCHAR(255),
@Table2 VARCHAR(255)
) AS

Exec( 'SELECT Credit FROM '  + @Table1 + ' UNION ' +
         'SELECT Credit FROM ' + @Table2 )
Go

SomeProcedure '[Company2003].[dbo].[Transactions]', '[Company2004.[dbo].[Transactions]'

Cut and Paste, and give it a try. It worked here.


0
lil_gustaAuthor Commented:
I appreciate for all the help posted so far...
However, like I previously said I need to work this out in a UDF because it's more complex
than a single field select. I'm using cursors (unfortunately) to populate results record by record depending on several clauses and it cannot be done within a Stored Procedure.

I'm aware that the exec does not work in the UDF and this is the major drawback.

If you could provide me with a solution to be used in a Function, then it's all cool.
0
rehandCommented:
my apologies, I missed the UDF part of the post.
0
rehandCommented:
I just wanted to toss this out.

What if you created a view that joined all relevant tables and included the database name. Then you could filter the view by passing the dbname parameter.
0
bukkoCommented:
lil_gusta,

This cannot be done using a UDF.
If you give a bit more info on exactly what you want to do I can probably supply a stored proc to do what you want.
What are your original inputs and your expected output?

Regards

bukko
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.