How to chain multiple T-SQL statements (separated by GO's) into a single call to SQL using SqlCommand

Question

I have a C# desktop app that calls various SQL Server stored procedures to perform various work of exporting and importing data to a SQL Server 2008 R2 database.

These all work fine, no problem. And my app calls them just fine with all parameters etc.

In order to "assist the user", I'm coding a button to add all the stored procedures to the configured database. To this end, I've created a script along the lines of:

USE [%DATABASENAME%]
GO        

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMyProc1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spMyProc1]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMyProc2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spMyProc2]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMyProc3]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spMyProc3]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spMyProc1]
        @VariousParams varchar(100),
    @ResultText varchar(4000) OUTPUT
AS
BEGIN
  -- Code removed for brevity
END

GO
--

CREATE PROCEDURE [dbo].[spMyProc2]
        @VariousParams varchar(100),
    @ResultText varchar(4000) OUTPUT
AS
BEGIN
  -- Code removed for brevity
END

GO
--

CREATE PROCEDURE [dbo].[spMyProc3]
        @VariousParams varchar(100),
    @ResultText varchar(4000) OUTPUT
AS
BEGIN
  -- Code removed for brevity
END

GO

When I run this in SQL Server Management Studio, it runs fine, no problems at all.

However in my C# app, an exception is thrown and I get a boat load of errors as follows:

Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@MessageText".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@SourceDataFolder".
Must declare the scalar variable "@SourceDataFolder".
Must declare the scalar variable "@SequenceNo".
Must declare the scalar variable "@UserID".
Must declare the scalar variable "@SequenceNo".
Must declare the scalar variable "@UserID".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@MessageText".
Must declare the scalar variable "@MessageText".
Must declare the scalar variable "@MessageText".
Incorrect syntax near 'GO'.
The variable name '@PS_DEFAULT' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@PS_ERROR_MSG' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@PS_ERROR_SEVERITY' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@SequenceNo".
Incorrect syntax near 'GO'.

(This is what's in the ex.Message as caught by the catch block in the code below).

My code is very straightforward as follows:

    bool retVal = false;
    string command = Properties.Resources.MyApp_StoredProcedures.ToString().Replace("%DATABASENAME%", Properties.Settings.Default.DBName);

    try
    {
        sqlCmd = new SqlCommand(command, csSQLConnection._conn);
        sqlCmd.ExecuteNonQuery();
        retVal = true;
    }
    catch (Exception ex)
    {
        retVal = false;
    }
    finally
    {
        sqlCmd.Dispose();
    }

(The replace above simply replaces the placeholder in the USE line at the top of the script and it works as I can see when I step through and over that line).

So basically, what am I doing wrong as the SQL itself seems fine?

Many thanks

Stool

You basically need to break up your script at every GO and execute each "sub-script" as a separate call to SqlCommand.ExecuteNonQuery(), in order to satisfy requirements like 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. – Lodging

Answer 1

This should be easy...

get rid of GO, that's SSMS specific syntax, the SQL language doesn't require or support it, rather you should terminate your individual create scripts with ; . Let me know how that goes.

Robyn

Thanks - that's removed all the errors relating to the GO statements, however all others remain. Any other ideas? The SQL is good, I know it is, as SSMS processes it all fine. – Stool

That won't work - since things like CREATE/ALTER PROCEDURE' must be the first statement in a query batch. aren't going to be fixed with this drop all GO approach .... – Lodging

I write my sp's as... CREATE PROCEDURE [dbo].[spMyProc1] (@VariousParams varchar(100), @ResultText varchar(4000) OUTPUT) notice the (). Try that and see if it resolves it. – Robyn

Answer 2

You CANNOT set a single ADO.NET object to execute a script that contains batch terminators("GO") as far as I know. You would have to do one of two things:

1. Create a SQL Management Studio Object and run it in the background. I know SQL Management Studio's folder has DLL's that can do work from SSMS. I have created .NET code to open SSMS for someone and be about to load it, but not execute it directly.

2. Do a for each method and break apart your SQL script to create an array of objects from the 'GO' statements into memory in a List or similar. Then iterate through that list with a 'foreach' statement to execute each one with the appropriate try/catch blocks.

Speer

Answer 3

If the script comes from a Stream (such as from an embedded resource via Assembly.GetManifestResourceStream(), from a FileStream, or a MemoryStream). You can use the following to split the SSMS SQLCMD batch scripts that can include GO:

public IEnumerable<string> GetScriptParts(Stream script)
{
    const string reBatchSeparator = @"^(/\*.\*/)?\s*GO\s*(/\*.*\*/)?\s*(--.*)?$";

    using (StreamReader sr = new StreamReader(script))
    {
        StringBuilder sb = new StringBuilder();
        while(!sr.EndOfStream)
        {
            string line = sr.ReadLine();
            if (!batchSeparator.IsMatch(line))
            {
                sb.AppendLine(line);
            }
            else
            {
                string part = sb.ToString();
                if (!string.IsNullOrEmpty(part))
                {
                    yield return part;
                }
                sb.Clear();
            }
        }

        string part = sb.ToString();
        if (!string.IsNullOrEmpty(part))
        {
            yield return part;
        }
    }
}

The method ExecuteBatch sequentially executes the script parts via SqlCommand:

public void ExecuteBatch(SqlConnection conn, Stream script)
{
    foreach (string part in GetScriptParts(script))
    {
        SqlCommand cmd = conn.CreateCommand();

        cmd.CommandText = part;
        cmd.ExecuteNonQuery();
    }
}

Caniff