Running dynamic SQL in a different database

9

May 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.

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.

9 thoughts on “Running dynamic SQL in a different database

  1. aaronbertrand says:

    I find this a lot easier:

    DECLARE @db sysname = N'otherDB';
    DECLARE @exec nvarchar(1000) = QUOTENAME(@db) + N'.sys.sp_executesql';

    EXEC @exec @sql;

  2. […] Kenneth Fisher doesn’t like this database and wants to move to a new one: […]

  3. Jen McCown says:

    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;

  4. CHRISTOPHER G says:

    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;

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,692 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: