Solved

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

Posted on 2004-04-27
14
659 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connect to an SQL server ActiveX 10 25
t-sql need help on t-sql 10 25
Amazon RDS migrate to SQL Server 3 24
SQL Find Carriage Return and Delete it. 3 11
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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