How to run query with parameters against a database whose name is in a variable using sp_ExecuteSql

Question

I am working with Microsoft SQL Server 2016 in Windows Server 2016.

I have a query with 1 parameter, string type.

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 = @ProjectName
order by 1, 2;

where @ProjectName is varchar(50) type.

I have to write a proc which receives 2 arguments: database name and project name:

@a_database_name varchar(100),
@a_project_name varchar(50)

I want to run the above query passing @a_project_name as parameter to the query. And I want to run the query against the database provided in @a_database_name variable using sp_executeSQL. How can I do that?

I know how to perform this task without sp_executeSQL. I want to know how to do it with sp_executeSQL?

Answer 1

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