How to remove GO from variable so it could be executed by sp_executesql

Question

I need to find a T-SQL way to remove GO command from scripts which I read from .sql files. I'm using similar to this approach Execute SQL scripts from file, which suits my needs very well, however some of the files contains GO command and breaks execution as sp_executesql doesn't handle non T-SQL commands. How to create a REPLACE which would remove GO, which mostly sits alone in the row? Or any other method I could apply here? Please keep in mind, that in the script there could be other GOs, which are actually not a command.

DECLARE @sql NVARCHAR(1000) = 
    'DECLARE @table AS TABLE(
        [Id] INT,
        [Info] NVARCHAR(100)
        );

    INSERT INTO @table
        ([Id],[Info])
    VALUES
        (1,''Info''),
        (2,''Show must go on''),
        (3,''GO'');

    SELECT * FROM @table;

    GO';

PRINT @sql;
EXEC sp_executesql @sql;

Using xp_cmdshell to execute scripts is not an option due to server security restrictions. SQLCMD is not an option too this time.

I think the real problem is that you're trying to use `sys.sp_executesql` as a replacement for something like `sqlcmd`. Removing the `GO`s could easily be a breaking change. — Thom A, Dec 02 '21 at 15:21
Trying to differentiate between string literals and reserved keywords is a tough nut that's going to rely on an awful lot of text parsing. What's the feasibility of editing your script files outside of SQL Server? — paneerakbari, Dec 02 '21 at 15:23
As another nail in the coffin, T-SQL is a terrible language for string manipulation. You would need to parse the batches, and then remove the `GO`s where appropriate. Honestly, that's an impossible task for the language. — Thom A, Dec 02 '21 at 15:24
If you need to execute a script with GO batch terminators programmatically, consider using the SMO API per [this answer](https://stackoverflow.com/questions/40814/execute-a-large-sql-script-with-go-commands). T-SQL isn't the right tool to parse scripts. — Dan Guzman, Dec 02 '21 at 15:33
How are you getting the command from your file into a T-SQL variable? — Aaron Bertrand, Dec 02 '21 at 15:42
You could pick up a [TSQL parser](https://github.com/bruce-dunwiddie/tsql-parser) and modify it to handle SQLCMD commands. That ought to be a reliable way to remove the offending `GO`. — HABO, Dec 02 '21 at 17:10

Shnugo · Accepted Answer · 2021-12-02T20:10:34.753

Well, I would NOT claim that this is the way this should be done, but it was some fun to ticker it down:

Disclaimer: This is a demonstration why TSQL is the wrong tool for this

I added some more GO-separated statements and used quotes within to get it even more tricky:

DECLARE @sql NVARCHAR(1000) = 
    'DECLARE @table AS TABLE(
        [Id] INT,
        [Info] NVARCHAR(100)
        );

    INSERT INTO @table
        ([Id],[Info])
    VALUES
        (1,''Info''),
        (2,''Show must go on''),
        (3,''This includes a GO and some "quoted" text''),
        (4,''GO'');

    SELECT * FROM @table;

    GO

    SELECT TOP 10  * FROM sys.objects
    GO
    PRINT ''Each GO will be used to have separate patches''';

--let's start by removing various kinds of line breaks

SET @sql = REPLACE(REPLACE(STRING_ESCAPE(@sql,'json'),'\r','\n'),'\n\n','\n');

--Using a CS_AS-collation will avoid to take "go" as we (hopefully!) can rely on "GO":

DECLARE @json NVARCHAR(MAX) =  CONCAT('["',REPLACE(REPLACE(@sql COLLATE Latin1_General_CS_AS,'GO' COLLATE Latin1_General_CS_AS,'","GO'),'\n','","'),'"]');

--Above I used each upper-case "GO" and each line break to separate the string.
--Doing so we transform your string into a json array
--Now we can fill this into a table using OPENJSON to read the json-array (ommitting empty lines)

DECLARE @tbl TABLE(RowIndex INT IDENTITY,fragment NVARCHAR(MAX));

INSERT INTO @tbl(fragment)
SELECT STRING_ESCAPE(A.[value],'json') 
FROM OPENJSON(@json) A
WHERE LEN(TRIM(A.[value]))>0 AND TRIM(A.[value])!=NCHAR(9);

--We need these variable for the cursor

DECLARE @patch NVARCHAR(MAX);

--Now I open a cursor
--We do this by running down a recursive CTE once again building up a json array.
--This time we will separate the strings when the upper-case "GO" is sitting alone in its line.

DECLARE cur CURSOR FOR

WITH cte AS
(
    SELECT RowIndex, CAST(CONCAT('["',fragment) AS NVARCHAR(MAX)) growingString  
    FROM @tbl WHERE RowIndex=1

    UNION ALL

    SELECT n.RowIndex
          ,CONCAT(cte.growingString,CASE WHEN TRIM(n.fragment) COLLATE Latin1_General_CS_AS=N'GO' THEN N'","' ELSE n.fragment END)
    FROM @tbl n 
    INNER JOIN cte ON n.RowIndex=cte.RowIndex+1  
)
,thePatches AS
(
SELECT TOP 1 CONCAT(growingString,'"]') AS jsonArray 
FROM cte ORDER BY RowIndex DESC
)
SELECT A.[value] AS patch
FROM thePatches p
CROSS APPLY OPENJSON(p.jsonArray) A;

--we can - finally - walk down the patches and execute them one by one

OPEN cur;
FETCH NEXT FROM cur INTO @patch;
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @patch; --PRINT out for visual control before execution!
    --EXEC(@patch);
    FETCH NEXT FROM cur INTO @patch;
END
CLOSE cur;
DEALLOCATE cur;

There are millions of things (e.g. line-breaks within content, commented sections, max recursion) which can destroy this approach. So clearly DO NOT follow this suggestion :-)

Neat but (as you acknowledge) brittle, e.g. `GO 10` is going to be a common thing in such deployment scripts, and that will either break this or eliminate the loop, I didn't test. As you and others have suggested, T-SQL just isn't the place to do this. :-) — Aaron Bertrand, Dec 02 '21 at 19:50
@AaronBertrand of course! you are right This was more sort of fun and a way to demonstrate why this should **not** be done with TSQL — Shnugo, Dec 02 '21 at 20:08

How to remove GO from variable so it could be executed by sp_executesql

1 Answers1