Running dynamic SQL in a different database
9May 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.