Solved

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

Posted on 2004-04-27
14
655 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:lil_gusta
  • 5
  • 3
  • 2
  • +2
14 Comments
 
LVL 3

Accepted Solution

by:
edwardsearch earned 75 total points
ID: 10927499
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
 
LVL 8

Assisted Solution

by:bukko
bukko earned 75 total points
ID: 10927759

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
 
LVL 4

Assisted Solution

by:rehand
rehand earned 75 total points
ID: 10927883


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
 

Author Comment

by:lil_gusta
ID: 10927993
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
 
LVL 4

Expert Comment

by:rehand
ID: 10928034
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
 
LVL 8

Expert Comment

by:bukko
ID: 10928646

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

bukko
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 75 total points
ID: 10928867
No to mention the fact that Exec is "verbotten" in UDF's.
0
 
LVL 4

Expert Comment

by:rehand
ID: 10928925

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
 

Author Comment

by:lil_gusta
ID: 10929083
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
 
LVL 4

Expert Comment

by:rehand
ID: 10929207
my apologies, I missed the UDF part of the post.
0
 
LVL 4

Expert Comment

by:rehand
ID: 10929367
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
 
LVL 8

Expert Comment

by:bukko
ID: 10957983
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now