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.