Ad:

MS SQL Discussion Thread
Unsolved
Views: 1193

Jan 5th, 2009

Searching in 2 databases

Expand Post »

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:

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

Similar Threads

SQL Injection Attack in Database Design
transfering data between databases in MS Access and FileMaker Pro
Searching multiple MS SQL Tables in VB.NET
PHP vs ASP... the big ShOwdOwN in IT Professionals' Lounge
Can we access Databases through C or C++ in C++

muek

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Dec 2008

Permalink

Jan 5th, 2009

Re: Searching in 2 databases

Yes, I tried it, did you?

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Jan 6th, 2009

Re: Searching in 2 databases

Yes I have tried, but doesn't run.

muek

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Dec 2008

Permalink

Jan 6th, 2009

Re: Searching in 2 databases

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

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Jan 7th, 2009

Re: Searching in 2 databases

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.

pclfw

Reputation Points: 33

Solved Threads: 9

Junior Poster

Offline

132 posts
since Jun 2005

Permalink

Jan 7th, 2009

Re: Searching in 2 databases

Look

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.

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Jan 7th, 2009

-1

Re: Searching in 2 databases

haa?

Attached Thumbnails

Click image for larger version

Name: select from different database.jpg
Views: 14
Size: 49.9 KB
ID: 8784

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Jan 7th, 2009

Re: Searching in 2 databases

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

muek

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Dec 2008

Permalink

Jan 7th, 2009

Re: Searching in 2 databases

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

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Jan 8th, 2009

Re: Searching in 2 databases

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

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.

stephen84s

Featured Poster

Reputation Points: 653

Solved Threads: 151

Nearly a Posting Virtuoso

Offline

1,316 posts
since Jul 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Connection is Busy with results for another hstmt ERROR

Next Thread in MS SQL Forum Timeline: selecting from other tables with information from the main table

DaniWeb Message

Cancel Changes

User Name
Password