Uncategorized

Running dynamic SQL on a database

You can call <dbname>.sys.sp_executeSQL to run dynamic SQL on a database, but what if you want to run dynamic SQL on a list of databases you have as strings?

I have used this to perform maintenance tasks on sets of related databases that could have different names (e.g. if they run multiple instances of our product’s databases on the same SQL server instance), with great success.

ALTER PROCEDURE [Admin].[Infrastructure_ExecuteSQL]
(
@sql NVARCHAR(MAX),
@dbname NVARCHAR(MAX) = NULL
)
AS BEGIN
/*
PURPOSE
Runs SQL statements in this database or another database.
You can use parameters.

TEST
EXEC dbo.Infrastructure_ExecuteSQL 'SELECT @@version, db_name();', 'MCSAuditLog';

REVISION HISTORY
20180803 DKD
Created
*/

/* For testing.
DECLARE @sql NVARCHAR(MAX) = 'SELECT @@version, db_name();';
DECLARE @dbname NVARCHAR(MAX) = 'mcslottery';
--*/

DECLARE @proc NVARCHAR(MAX) = 'sys.sp_executeSQL';
IF (@dbname IS NOT NULL) SET @proc = @dbname + '.' + @proc;

EXEC @proc @sql;

END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s