Searching in 2 databases

[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

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

Without using dymanic SQL.

muek

[Ramy Mahrous]

Yes, I tried it, did you?

[muek]

Yes I have tried, but doesn't run.

[Ramy Mahrous]

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

[pclfw]

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.

[Ramy Mahrous]

Look :)

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'

[Ramy Mahrous]

haa?

[muek]

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

[Ramy Mahrous]

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

[stephen84s]

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

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.