Using "USE" for multiple database with "UNION ALL"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using "USE" for multiple database with "UNION ALL"

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-24 : 12:34:49

My plan is to create a single SP that pulls data from 3 different database with different parameters for each database. My plan is instead of creating 3 different for SP, I want to combine all into one and then use UNION ALL.

Below is what I have in mind, but it does not work. I get the error when it goes to the query "USE".

Any ideas?

Example:


CREATE PROCEDURE TAX_AMOUNT_SP

@STARTDATE AS SMALLDATETIME,
@ENDDATE AS SMALLDATETIME

AS

USE CDDB

SELECT 	A.ID, A.AMOUNT, A.DATE, A.ACCOUNT
FROM 	ACCOUNT A
WHERE 	A.ID LIKE 'CD%'
	AND A.ACCOUNT LIKE '233%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE
	OR (A.ID LIKE 'FG%'
	AND A.ACCOUNT LIKE '013%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE)

UNION ALL

USE  ACCTDB

SELECT 	A.ID, A.AMOUNT, A.DATE, A.ACCOUNT
FROM 	ACCOUNT A
WHERE 	A.ID LIKE 'ACCT%'
	AND A.ACCOUNT LIKE '034%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE
	OR (A.ID LIKE 'CM%'
	AND A.ACCOUNT LIKE '893%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE)

UNION ALL

USE  SALESDB

SELECT 	A.ID, A.AMOUNT, A.DATE, A.ACCOUNT
FROM 	ACCOUNT A
WHERE 	A.ID LIKE 'SALES%'
	AND A.ACCOUNT LIKE '039%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE
	OR (A.ID LIKE 'HG%'
	AND A.ACCOUNT LIKE '332%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE)
ORDER BY A.ACCOUNT, A.DATE, A.ID

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-24 : 13:23:18

Just use 3 part naming.

SELECT ... FROM CDDB.dbo.ACCOUNT
UNION ALL
SELECT ... FROM ACCTDB.dbo.ACCOUNT

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-24 : 13:23:37

maybe


USE CDDB

SELECT 	A.ID, A.AMOUNT, A.DATE, A.ACCOUNT
FROM 	CDDB.dbo.ACCOUNT A
WHERE 	A.ID LIKE 'CD%'
	AND A.ACCOUNT LIKE '233%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE
	OR (A.ID LIKE 'FG%'
	AND A.ACCOUNT LIKE '013%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE)

UNION ALL

USE  ACCTDB


SELECT 	A.ID, A.AMOUNT, A.DATE, A.ACCOUNT
FROM 	ACCTDB.dbo.ACCOUNT A
WHERE 	A.ID LIKE 'ACCT%'
	AND A.ACCOUNT LIKE '034%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE
	OR (A.ID LIKE 'CM%'
	AND A.ACCOUNT LIKE '893%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE)

UNION ALL

USE  SALESDB

SELECT 	A.ID, A.AMOUNT, A.DATE, A.ACCOUNT
FROM 	SALESDB.dbo.ACCOUNT A
WHERE 	A.ID LIKE 'SALES%'
	AND A.ACCOUNT LIKE '039%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE
	OR (A.ID LIKE 'HG%'
	AND A.ACCOUNT LIKE '332%'
	AND A.DATE BETWEEN @STARTDATE AND @ENDDATE)
ORDER BY A.ACCOUNT, A.DATE, A.ID

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-24 : 13:37:31

wow!! Thanks. That didn't occur to me and I was thinking way too hard. LOL!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources