You can use below SQL to create that procedure:
create procedure ProcName @a_database_name varchar(100),@a_project_name varchar(50)
as
begin
EXEC('USE ' + @a_database_name + ';select PH.project_name, PD.employee_id, E.first_name, E.last_name
from project_header PH
inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= '+@a_project_name+' order by 1, 2;')
end
But it has SQL injection risk- if @a_database_name
is provided contains in it ";DROP DATABASE"
. But if you trust the system that is calling this procedure you are good to go.
Or you might use below query without "use databasename" in it:
create procedure ProcName @a_database_name varchar(100),@a_project_name varchar(50)
as
begin
EXEC('select PH.project_name, PD.employee_id, E.first_name, E.last_name
from '+@a_database_name+'.dbo.project_header PH
inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= '+@a_project_name+' order by 1, 2;')
end
Query with sp_executesql:
create procedure ProcName @a_database_name varchar(100),@a_project_name varchar(50)
as
begin
DECLARE @SqlStatment AS NVARCHAR(1000)
SET @SqlStatment = 'select PH.project_name, PD.employee_id, E.first_name, E.last_name
from '+@a_database_name+'.dbo.project_header PH
inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= @a_project_name order by 1, 2;'
EXECUTE sp_executesql @SqlStatment ,N'@a_project_name varchar',@a_project_name
end
Revised Answer:
create procedure ProcName @a_database_name varchar(100),@a_project_name varchar(50)
as
begin
declare @sp_executesql as nvarchar(1000)
SELECT @sp_executesql = quotename(@a_database_name) + '.sys.sp_executesql'
EXEC @sp_executesql N'select PH.project_name, PD.employee_id, E.first_name, E.last_name
from project_header PH inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= @a_project_name order by 1, 2;',N'@a_project_name nvarchar(100)', @a_project_name;
end
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…