Blog Post

Running dynamic SQL in a different database

,

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.

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

5 (2)

Share

Share

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

5 (2)