Running dynamic SQL in a different database
10May 19, 2022 by Kenneth Fisher
This is one of those things that on hindsight was a stupid problem, but still cost me hours and a major headache. So I thought I would share the story of my headache with you. 😁
A few weeks ago I was working on some dynamic SQL that hit multiple databases. Not a huge issue. I do lots of dynamic SQL.
DECLARE @sql nvarchar(max);
SET @sql = N'USE DBName;
GO
/* Do stuff here. */
SELECT * FROM sys.tables;'
EXEC sp_executesql @sql;
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘GO’.
Completion time: 2022-05-18T16:53:57.5145414-05:00
Hu? I can’t use GO in my dynamic SQL? Well then how am I going to switch databases before I run my code?
Two mistakes here. I was going to say they were stupid mistakes, but let’s face it, everyone forgets things and gets stuck on something simple. So mistakes, but not stupid. Anyway.
- Mistake 1) You can’t use GO in dynamic SQL. That would be because GO is a batch separator and not actually part of T-SQL.
- Mistake 2) I am so in the habit of putting a GO after every USE that I forgot it’s not really necessary.
I banged my head against my desk for probably 3 hours, doing all kinds of weird searches, trying out all kinds of strange pieces of code, for something I knew was really really simple.
DECLARE @sql nvarchar(max);
SET @sql = N'USE DBName;
/* Do stuff here. */
SELECT * FROM sys.tables;'
EXEC sp_executesql @sql;
I ended up with a few takeaways from all of this.
- Habits can really mess you up if you aren’t careful.
- You don’t actually need a GO after a USE.
- Everyone makes mistakes. And frequently those mistakes are from some really simple stuff. No one is immune.
FYI I already knew that last one, but it’s one of those things that everyone should be reminded of periodically.
SQL Studies 
I find this a lot easier:
DECLARE @db sysname = N'otherDB';DECLARE @exec nvarchar(1000) = QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @exec @sql;
I like it! Never thought of doing it that way before.
[…] Kenneth Fisher doesn’t like this database and wants to move to a new one: […]
Is there any reason not to do this?
DECLARE @sql nvarchar(max);
SET @sql = N’ /* Do stuff here, always with DBName.” . */
SELECT * FROM [OtherDB].sys.tables;’
EXEC sp_executesql @sql;
I’d say it depends on how complex “do stuff here” is. Also there are always those functions that only give you information on your current database. FILEPROPERTY for example.
True, true.
Some commands have to be the first statement in a batch, so you can’t always use a USE statement to change DBs first. To get around that, I went Inception-style on the problem and created this little stored procedure:
CREATE PROCEDURE dmc.ExecuteInDB
@p_SQLToExecute nvarchar(max)
, @p_RunInDB sysname = NULL
AS
——————————————————————————–
— CONSTANTS AND VARIABLES —
—————————–
DECLARE @PARMDEF nvarchar(max) = ‘@SQLIn nvarchar(max)’;
DECLARE @SQL nvarchar(max);
——————————————————————————–
— EXECUTE —
————-
SET @SQL = N’USE [‘ + @p_RunInDB + N’];’ + Char(13) + Char(10);
SET @SQL = IsNull (@SQL, N”) + N’EXEC sp_executesql @SQLIn;’;
EXEC sp_executesql @SQL, @PARMDEF, @SQLIn = @p_SQLToExecute;
You might also try looking at Aaron’s solution above. It’s my favorite so far.
I had to read Aaron’s twice to absorb that. It’s my favorite now too.
Many thanks, Aaron.
My job requires the exection of SQL code blocks across 1000’s of dbs on dozens of servers to collect and warehouse data, sometimes the code is a static/common proc/snipet, but many times it is a custom request.. “Find me all of {this condition….}, and search ONLY these customers/DBs”
As a result I/We have create many “Code Generation” methods to run SQL across multiple servers/db’s.
The most comman is an LOOP based [Site list] method, not the fastest, but VERY stable/dependable, when time is not a factor, as in run off hours/overnight(s).
Here is a simplified version:
Note: This method depends on the use of [Linked Servers] from the calling Server/DB, and proper exection permission, as well as REMOTE EXECUTION… {Argue the good/bad of all that if you will -:)}
/* ========================================================================
History
———-
YYYY-MM-DD WHO [Comment]
======================================================================== */
SET NOCOUNT ON;
— ================ COMMON VARS
Declare @RowCount Int — Output/Log
Declare @Dump Varchar(200) — Output/Log
— ================ Customer/Site Info variables
Declare @SiteKey Int — Unique Prime Key
Declare @Nickname Varchar(100) — Customer Nickname (Display)
Declare @DBName Varchar(100) — Database Name
Declare @ExecuteServer Varchar(200) — Remote Exec string
— ================ SQL Parts
Declare @SQLExec nVarchar(Max) — Runable SQL string
Declare @SQLTemplate Varchar(Max) = ‘
USE [{DB NAME}]
/* =======================================================
Customer: {NICKNAME}
CODE COMMENTS IN GENERATED SQL
======================================================= */
BEGIN
— [SQL COMMAND(S) HERE].. LIKE W/REPLACE
Select {SITEKEY} [SiteKey] — PK
, ”{NICKANME}” [Customer]
, [FIELD1]
, [FIELD2]
FROM [dbo].[Tablename]
JOIN — ??? ON [] = []
WHERE ??????????
END
‘
/* ========== POSTING COMMENT
Master list of Customers DBs – Common location all DB’s can access
– Common.dbo.Sites {Linked Server}
– Key/Cust Nickname(Short)/Customer Type(Filters)/
– DB Server/DB Name – USE/Remote Exec
– Other info (+30 attributes)
=========================== */
Declare @Sites dbo.typSiteList_Table
Insert Into @Sites
EXEC [dbo].[Master_Get_SiteList_Table] ‘{**FILTER ARG**}’
— ADD Additional FILTER/DELETEs of SITE(s) HERE
WHILE Exists(Select Top 1 [SiteKey] From @Sites) — Loop on Site List
BEGIN
— ==================== Get values for SQL Generation & Display
Select TOP 1 @SiteKey = [Site].[SiteKey],
@Nickname = [Site].[Nickname],
@DBName = [Site].[DBName],
@ExecuteServer = ‘[‘ + [Site].[DBServer] + ‘].[master].dbo.sp_executesql’
From @Sites [Site]
Order By [Site].[SiteKey]
— LOG
Set @Dump = ‘– ‘ + CONVERT(Varchar(50),GetDate(),121)
+ ‘ – ‘ + Right(‘ ‘ + CONVERT(Varchar(4),@SiteKey),5) + ‘ ‘
+ Convert(Char(30),@Nickname)
Print @Dump
/* ========== POSTING COMMENT
Token based replacement in @SQLTemplate
=========================== */
Set @SQLExec = Replace(Replace(Replace(
@SQLTemplate
, ‘{SITEKEY}’ , @SiteKey)
, ‘{NICKNAME}’ , @Nickname)
, ‘{DB NAME}’ , @DBName)
Print @SQLExec — TESTING
EXEC @ExecuteServer @SQLExec
Set @RowCount = @@ROWCOUNT
Set @Dump = ‘ – Output: ‘ + CONVERT(VARCHAR(20),@RowCount)
Print @Dump — LOGGING
NEXT_SITE:
Set @Dump = ‘– ‘ + CONVERT(Varchar(50),GetDate(),121) + ‘ – ‘ + Right(‘ ‘ + CONVERT(Varchar(4),@SiteKey),5) + ‘ ‘ + Convert(Char(30),@Nickname) + ‘ Complete’
Print @Dump
Delete From @Sites Where [SiteKey] = @SiteKey — Remove This Site From List as Complete
END
*** Pasted poorly, not sure how to make that better..