Transactsql to run another TransactSQL script

sql

sqlcmd-mode

dynamic-execution

script-automation

byAlex Kataev·Nov 26, 2024

Feeling a bit lazy and wanting your T-SQL script to execute another? Use sp_executesql like so:

DECLARE @SQLContent NVARCHAR(MAX)
-- Load your T-SQL script into the variable like a sneaky little spy 🕵️
SELECT @SQLContent = BulkColumn FROM OPENROWSET(BULK 'script.sql', SINGLE_BLOB) AS TSQLScript
-- Run this baby now! 🏃
EXEC sp_executesql @SQLContent

Guess what? Just point it at 'script.sql', load it up, and then run it. Remember to give the SQL Server service user access rights unless you like surprises.

The road less travelled: alternative methods

Scripting like Shakespeare in SQLCMD Mode

Is the SQLCMD Mode the Shakespeare of script automation? Yes.

Rise to the occasion by enabling SQLCMD Mode in SSMS.
Invoke other scripts like a wizard with the :r directive.
Use :setvar to define path variables (think marauder's map for scripts).
Script executions can be as smooth as butter with this sequence. Especially handy if the upgrade feels like a train wreck.

Being a puppet master with EXEC

Feeling in control? Execute SQL dynamically with EXEC:

DECLARE @path NVARCHAR(256) = N'D:\Scripts\MyScript.sql',  -- The chosen one!
        @SQL NVARCHAR(MAX)   -- The empty vessel!

SELECT @SQL = BulkColumn   -- Let the filling begin!
FROM OPENROWSET(BULK @path, SINGLE_BLOB) AS script

EXEC(@SQL)   -- And, ACTION!

The OPENROWSET function is now your script-fetching minion!

Mastering the chaos: organized scripting

Avoid going into the jungle without a map:

Store scripts in a clean directory.
Use versioning like a pro.
Separate directories for different environments is a lifesaver.

Get, set, fetch (scripts)! - scripting challenges

Know your beast (challenges):

Are you ready to live on the edge? Use XP_CMDSHELL.
To read or not to read (script files) - permissions are a must.
Dodge the bullets - use TRY/CATCH blocks.
Shooting in the dark? Use the PRINT statement to light it up.

Sherlock's guide to SQL mysteries

Automating, Autobots style

Bored? Automate regular execution using stored procedures. Be the Megatron of your SQL universe.

CREATE PROCEDURE RunMyScript AS
BEGIN
    -- Vacation time! Let's automate the boring stuff.
END

Feel like a boss, invoke it using SQL Server Agent or just wave (type) when you need it.

Getting under T-SQL's skin with Debugging

Are you questioning your life... I mean, your dynamic SQL? Use PRINT before EXEC

PRINT @SQL  -- "Mirror, Mirror on the wall, Who's the fairest SQL of all?"
EXEC(@SQL)

Now execute with confidence. Go forth and conquer!

Use cursors for checklist-style execution

Have loads of SQL scripts that follow a particular order? They're like dominos.

DECLARE curScripts CURSOR FOR
SELECT scriptPath FROM MyScripts WHERE condition ORDER BY sequenceNo   -- Here's our dominos line-up

OPEN curScripts     -- Let's get this party started!
FETCH NEXT FROM curScripts INTO @path   -- Next one, please!

WHILE @@FETCH_STATUS = 0   -- Keep it rolling!
BEGIN
    -- Load and execute each script  -- Here we go!
END

CLOSE curScripts
DEALLOCATE curScripts   -- Afterparty cleaning time!