Running a CREATE PROCEDURE script programmatically

Question

I'm trying to develop a little app that will run a CREATE PROCEDURE script. A simple example:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_get_roles_in_system')
    BEGIN
        DROP  Procedure  [dbo].[ap_get_roles_in_system]
    END

GO

CREATE PROCEDURE [dbo].[ap_get_roles_in_system]
(
    @system_id int
)
AS
    SELECT * FROM roles

GO

GRANT EXEC ON [dbo].[ap_get_roles_in_system] TO PUBLIC

GO

If I load this text up in a string, and run it by way of ExecuteNonQuery(), the first item, which drops the stored procedure, works fine, but instead of running the Create it finds a syntax error with the parameter of the stored procedure, namely: it hasn't been declared.

In short, instead of trying to run the CREATE, it is somehow trying to run the script as a script, not a CREATE. Not sure what the correct wording would be.

The script above works great if pasted into Sql Management Studio.

Here's the code I am executing:

public string RunSql(string Sql)
{
    string result = string.Empty;

    _conn.Open();
    SqlCommand cmd = new SqlCommand(Sql, _conn);
    cmd.CommandType = CommandType.Text;

    try
    {
        cmd.ExecuteNonQuery();

        result = "Succeeded";
    }
    catch (SqlException ex)
    {
        result = ex.Message;
    }

    return result;
}

As an alternative to your current approach, you could call sqlcmd.exe from .NET or a batch file. It is one of the tools that understands GO. You might also find its support for scripting variables useful for running scripts with multiple servers or databases. — Pondlife
– Pondlife, Commented Feb 8, 2013 at 19:37
Well, there is another subject. sqlcmd is definitely worth exploring. — Cyberherbalist
– Cyberherbalist, Commented Feb 11, 2013 at 18:01

Cyberherbalist · Accepted Answer · 2015-01-17 00:38:34Z

@RichardSchneider's answer led me to the solution I found, but I thought at this late date, since there have been so many views, that I should post the code that solved the problem, which Richard's answer led me to. Here it is, an entire class named SqlAction. Note that I split the entire text with "GO", and then put the components into an array, executing each component in turn:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SProcRunner
{
    public class SqlAction
    {
        public SqlAction(string connString)
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connString);

            _conn = new SqlConnection(sb.ToString());
        }

        private SqlConnection _conn;

        public string RunSql(string Sql)
        {
            string result = string.Empty;

            // split the sql by "GO"

            string[] commandText = Sql.Split(new string[] { String.Format("{0}GO{0}", Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);

            _conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _conn;
            cmd.CommandType = CommandType.Text;

            for (int x = 0; x < commandText.Length; x++)
            {
                if (commandText[x].Trim().Length > 0)
                {
                    cmd.CommandText = commandText[x];
                    try
                    {
                        cmd.ExecuteNonQuery();
                        result = "Command(s) completed successfully.";
                    }
                    catch (SqlException ex)
                    {
                        result = String.Format("Failed: {0}", ex.Message);
                        break;
                    }
                }
            }

            if (_conn.State != ConnectionState.Closed) _conn.Close();

            return result;
        }
    }
}

I had to use this to make it work for me. GetSprocFromFile(sprocFileName).Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries)

Richard Schneider · Accepted Answer · 2013-02-07 18:22:03Z

2

Remove the "GO" lines from the SQL.

answered Feb 7, 2013 at 18:22

Richard Schneider

35.5k9 gold badges59 silver badges74 bronze badges

2 Comments

Cyberherbalist Over a year ago

Close but not quite far enough, actually. Removing GO from the script causes another error when you hit the CREATE PROCEDURE. This throws an error message that says the CREATE/ALTER must be the first command in the script. What worked finally was to split the script into sections bounded by GO, and execute each section independently. But your answer clued me in to the final solution.

Cyberherbalist Over a year ago

I've added the code that your suggested answer led me to.

Collectives™ on Stack Overflow

Running a CREATE PROCEDURE script programmatically

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related