Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-04-27
14
Medium Priority
?
678 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
14 Comments
 
LVL 3

Accepted Solution

by:
edwardsearch earned 300 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 300 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 300 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

688 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