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

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.