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.table1Without using dymanic SQL.
muek
Yes, I tried it, did you?
Yes I have tried, but doesn't run.
Look, it works from Master database, and give me the SQL script and the error you get when you run it
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.
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'RamyMahrous please SEE my example.
It's completely different from yours.
oooh, sorry, really sorry I didn't notice that!!
You can't do such thing :$
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.