Solved

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

Posted on 2004-04-27
14
653 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

19 Experts available now in Live!

Get 1:1 Help Now