Create a stored procedure in my own SQL Server database

Question

I'm developing a website that I do not have remote access to its SQL Server. When I try to create a new procedure in my own database I got stuck this error:

CREATE/ALTER PROCEDURE must be the first statement in a query batch.

That's because of T-SQL scripts containing "GO" statement....

Using the Server Management Objects (SMO), the problem still exists for me. How can I create a stored procedure in my database without using Go statement and without using SMO?

This is my code:

    SqlConnection Con = new SqlConnection(ConnectionString);
    Con.Open();
    SqlCommand cmd;
    string sql = "CREATE Procedure [dbo]......";
    cmd = new SqlCommand(sql, Con);
    cmd.ExecuteNonQuery();
    Con.Close();

Thanks.

Have you tried Execute function? Also is it possible to share sql? — Hassan, May 10 '14 at 18:47
Please, check this [question](http://stackoverflow.com/questions/8659661/creating-a-stored-procedure-via-c-sharp) and the accepted [answer](http://stackoverflow.com/a/8659680/982431). The only difference that I can see with your code is that you don't have the `cmd.CommandType = CommandType.Text;` code statement. — HuorSwords, May 10 '14 at 19:00
@HuorSwords I know but when I remove the "USE" and "GO" line, created SP does not in the my DB. — n.y, May 10 '14 at 19:08
@Nasser, I'm not absolutetly sure, but you can define your default database on the connection string. If you define this default database, the `USE` sentence is not neccessary. In example, `Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword;` — HuorSwords, May 10 '14 at 19:10
I tried it once but I would try one more time. I'll tell you the results. — n.y, May 10 '14 at 19:16
I had read it somewhere that one must use "GO" statement (one liner) before `Create Procedure` statement as well. — Hassan, May 10 '14 at 19:19

score 0 · Accepted Answer · answered May 10 '14 at 21:27

After much searching I finally found the answer. You can change the current database for an open SqlConnection very easily:

connection.ChangeDatabase("YourDB");

An example:

private static void ConctDatabase(string connectionString)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        MessageBox.Show("Database: {0}", conn.Database);
        conn.ChangeDatabase("Northwind");
        MessageBox.Show("Database: {0}", conn.Database);
    }
}

Create a stored procedure in my own SQL Server database

1 Answers1