MS SQL

Searching in 2 databases

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2008

Posts: 9

Reputation: muek is an unknown quantity at this point

Solved Threads: 0

muek

Offline

Newbie Poster

Searching in 2 databases

Jan 5th, 2009

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

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Without using dymanic SQL.

muek

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: Searching in 2 databases

Jan 5th, 2009

Yes, I tried it, did you?

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Dec 2008

Posts: 9

Reputation: muek is an unknown quantity at this point

Solved Threads: 0

muek

Offline

Newbie Poster

Re: Searching in 2 databases

Jan 6th, 2009

Yes I have tried, but doesn't run.

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: Searching in 2 databases

Jan 6th, 2009

Look, it works from Master database, and give me the SQL script and the error you get when you run it

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Jun 2005

Posts: 60

Reputation: pclfw is an unknown quantity at this point

Solved Threads: 5

pclfw

Offline

Junior Poster in Training

Re: Searching in 2 databases

Jan 7th, 2009

Ramy,

I tried this and it does not run.

You cannot just concatonate local variables and literals and expect them to combine into another literal.

The best that you could do is to set a char / varchar variable to the whole sql string that you want to run and then use EXEC to run that code. But the poster said he didn't want dynamic SQL.

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: Searching in 2 databases

Jan 7th, 2009

Look

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
USE Master
GO
CREATE proc GetResult
@studentFirstName VARCHAR
AS
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Grade]
      ,[dt]
  FROM [BlogEngine].[dbo].[Student]
WHERE [FirstName] = @studentFirstName
UNION
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Grade]
      ,[dt]
  FROM [MaxMinManufacturingDM].[dbo].[Student]
WHERE [FirstName] = @studentFirstName
 
GetResult 'a'
Use Master
GO
Create proc GetResult
@studentFirstName varchar
AS
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Grade]
      ,[dt]
  FROM [BlogEngine].[dbo].[Student]
WHERE [FirstName] = @studentFirstName
UNION
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Grade]
      ,[dt]
  FROM [MaxMinManufacturingDM].[dbo].[Student]
WHERE [FirstName] = @studentFirstName

GetResult 'a'

Last edited by Ramy Mahrous; Jan 7th, 2009 at 11:27 am.

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: Searching in 2 databases

-1

Jan 7th, 2009

haa?

Attached Thumbnails

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Dec 2008

Posts: 9

Reputation: muek is an unknown quantity at this point

Solved Threads: 0

muek

Offline

Newbie Poster

Re: Searching in 2 databases

Jan 7th, 2009

RamyMahrous please SEE my example.
It's completely different from yours.

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: Searching in 2 databases

Jan 7th, 2009

oooh, sorry, really sorry I didn't notice that!!
You can't do such thing

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Jul 2007

Posts: 1,175

Reputation: stephen84s is a glorious beacon of light

Solved Threads: 125

stephen84s stephen84s is offline

Offline

Veteran Poster

Re: Searching in 2 databases

#10

Jan 8th, 2009

Directly your stored procedure would not work, you will need to generate a dynamic SQL statement like this:-

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE proc dbo.uspMySearch (
  @database1 nvarchar(50),
  @database2 nvarchar(50)
)
as
 
  SET nocount on
 
  execute 'select *
  from ' + @database1 + '.dbo.table1
  UNION
  select *
  from ' + @database2 + '.dbo.table1'
create proc dbo.uspMySearch (
  @database1 nvarchar(50),
  @database2 nvarchar(50)
)
as

  set nocount on

  execute 'select *
  from ' + @database1 + '.dbo.table1
  UNION
  select *
  from ' + @database2 + '.dbo.table1'

Note I haven't really tried the code out, but this is how I used execute dynamic SQL Queries.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand."

"How to ask questions the smart way ?"