0

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

4
Contributors
9
Replies
10
Views
8 Years
Discussion Span
Last Post by stephen84s
0

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.

0

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'
-1

haa?

Votes + Comments
The poster has not properly read either the starting post or the post he is replying to. He is acting very arrogant with this post. Not good at all for the newbies in the forum.
Attachments select_from_different_database.jpg 49.88 KB
0

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

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.