CODEWITHSUNDEEP
sql-servert-sqldynamic
Chip Wood
Chip Wood

Reputation: 1304

Dynamic Database Name for T-SQL statement FROM clause

I have several databases which contains the same table name and structure. I need to be able to execute a simple T-SQL which will run against each one and I would like to have the database name in the FROM clause populated using a variable. Can I do that or is there a better method?

Here is an example of code which will not work but should exemplify what I am trying to do.

DECLARE @dbn sysname;

SET @dbn = 'EDIT'

select sde_id, owner, direct_connect, nodename + ':' + sysname "nodename:sysname", start_time
  FROM **[@dbn]**.[SDE].[SDE_process_information]

Upvotes: 4

Views: 5953

Answers (2)

granadaCoder
granadaCoder

Reputation: 27906

You might use dynamic sql. At the same time, you want to use it responsibly.

If you have parameters that can contribute to plan-reuse, you should integrate them.

Here is a generic Northwind (microsoft sample db) example:

Dynamic sql is a blessing and a curse. Don't go crazy with it. Please, other developers are begging you.

See this as well: http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

"I can’t stress the importance of plan reuse enough these days. Too many new development efforts utilize a framework that centers on the ad hoc building of T-SQL statements that are submitted to the database engine as a nonparameterized batch. Although this might make the development effort slightly easier, it negatively affects database performance and scalability."

DECLARE @SQL NVARCHAR(500), @DatabaseName NVARCHAR(100), @Schema NVARCHAR(128),
@Table NVARCHAR(128), @OrderID INT, @EmployeeID INT ;
SET @DatabaseName = 'Northwind'; -- 'NorthwindPartial'
SET @Schema = 'dbo'
SET @Table = 'Employees' ;
SET @EmployeeID = 3 ;

select 'below probably does plan-reuse'
SET @SQL = 'SELECT EmployeeId ' + ' FROM ' + @DatabaseName + '.' + @Schema + '.' + @Table + ' WHERE EmployeeID > @P1' ;
select @sql as sp_executesqlStatement
EXEC sp_executesql @SQL, N'@P1 INT', @P1 = @EmployeeID ;

GO

Upvotes: 0

Sunil
Sunil

Reputation: 21416

Your query is not clear and all jumbled up, but you need to follow an approach as in code below. In this approach, you need to first create the query based on variables like database name in your case, and then execute the resulting query using sp_executesql.

DECLARE @dbn nvarchar(1000);

SET @dbn = N'EDIT';

declare @qry nvarchar(max);

set @qry = N'select sde_id, owner, direct_connect, nodename ,sysname , start_time
  FROM ' + @dbn + N'.[SDE].[SDE_process_information]';

execute sp_executesql @qry;

Upvotes: 4

Related Questions