Searching in 2 databases - SQL Server Forums

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

Transact-SQL (2005)

Searching in 2 databases

Author

Topic

muek
Starting Member

16 Posts

Posted - 2009-01-05 : 06:45:00

Hi,

I would like to know if it's possible to create a procedure, where we pass 2 arguments, the databases where the search must be done and how to use those parameters in select clause

muek

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 06:49:17

Yes you can.

create proc dbo.uspMySearch
(
@param1 int,
@param2 int
)
as

set nocount on

select *
from table1
where (@param1 is null or @param1 = col1)
and (@param2 is null or @param2 = col2)

E 12°55'05.63"
N 56°04'39.26"

muek
Starting Member

16 Posts

Posted - 2009-01-05 : 07:26:21

I didn't explain myself.

What I would like to know is if it's possible something like:


create proc dbo.uspMySearch
(
@database1 nvarchar(50),
@database2 nvarchar(50)
)
as

set nocount on

select *
from @database1.dbo.table1
UNION
select *
from @database2.dbo.table1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 07:29:14

Yes you can, but it will only demonstrate the poor application design.
See http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.63"
N 56°04'39.26"

muek
Starting Member

16 Posts

Posted - 2009-01-05 : 08:47:40

Hi Peso,

you are right, it's a bad design, but the real use of this question will be to compare 2 databases and report the differences between them.

Do you have any solutions for that question?
Anything good in the Internet good and free?

muek

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:02:10

quote:
Originally posted by muek

Hi,

I would like to know if it's possible to create a procedure, where we pass 2 arguments, the databases where the search must be done and how to use those parameters in select clause

muek

why does you've to select db's dynamically?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:15:26

quote:
Originally posted by muek

Hi Peso,

you are right, it's a bad design, but the real use of this question will be to compare 2 databases and report the differences between them.

Do you have any solutions for that question?
Anything good in the Internet good and free?

muek

http://msdn.microsoft.com/en-us/library/aa833428(VS.80).aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:15:47

also

http://www.mssqlcity.com/FAQ/Admin/CompareDB.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources