SQL CLR executes with multiples sql statements

Question

I need a SQL CLR that executes with multiple sql statements, i.e., with update queries, select queries and stored procedures to create a stored procedure in the database

[Microsoft.SqlServer.Server.SqlProcedure]
public static void pExecuteAuthorization(SqlInt32 id) {
string name = "";
string surname = "";

using (SqlConnection connection = new SqlConnection("context connection=true")) {

    string sql = "UPDATE tTest SET Sequence = ISNULL(Sequence,0) + 1 WHERE Id = " + id;

    using (SqlCommand command = new SqlCommand(sql, connection)) {

        connection.Open();

        int valRet = command.ExecuteNonQuery();

        if (valRet > 0)
        {
            command.CommandText = "SELECT Name, Surname  FROM tAuthorization WHERE Id = 1";
            SqlParameter param = command.Parameters.Add("@Name", SqlDbType.VarChar, 50);
            param.Direction = ParameterDirection.Output;
            param = command.Parameters.Add("@Surname", SqlDbType.VarChar, 20);
            param.Direction = ParameterDirection.Output;
            command.ExecuteNonQuery();

            name = command.Parameters["@Name"].Value.ToString();
            surname = command.Parameters["@Surname"].Value.ToString();

            if (name != "" && surname != "")
            {
                 command.CommandText = "SELECT ...";
                SqlDataReader dr = command.ExecuteReader();

                if (dr.Read())
                {


                //other queries more
                }
            }
        }

    }
    SqlContext.Pipe.ExecuteAndSend(command);
  }
}

But when I execute the sp created, only I get the result of select, and it does not execute the other queries

The CLR will run whatever you have coded, doesn't even have to be SQL. I would guess the issue is this line **if (name != "" && surname != "")** since those variable will always be empty. This makes your **//Other queries here**, not run. — Steve, Aug 01 '14 at 22:55
I've never tried it but this tells you how to do it: http://msdn.microsoft.com/en-us/library/ms165051.aspx — Steve, Aug 04 '14 at 14:14

SQL CLR executes with multiples sql statements

0 Answers0