When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?

According to the tests in this blog post SQL Server will do the parameterization for you, by wrapping your statement in sp_executesql, when you use CommandType.Text. But when you use CommandType.StoredProcedure you will parameterize it and thereby saving the database some work. The latter method is faster.

Edit:

Setup

I've done some tests myself and here are the results.

Create this procedure:

create procedure dbo.Test
(
   @Text1 varchar(10) = 'Default1'
  ,@Text2 varchar(10) = 'Default2'
)
as
begin
   select @Text1 as Text1, @Text2 as Text2
end

Add a trace to it using SQL Server Profiler.

And then call it using the following code:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main()
        {
            CallProcedure( CommandType.Text );
            CallProcedure( CommandType.StoredProcedure );
        }

        private static void CallProcedure(CommandType commandType)
        {
            using ( SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;") )
            {
                connection.Open();
                using ( SqlCommand textCommand = new SqlCommand("dbo.Test", connection) )
                {
                    textCommand.CommandType = commandType;
                    textCommand.Parameters.AddWithValue("@Text1", "Text1");
                    textCommand.Parameters.AddWithValue("@Text2", "Text2");
                    using ( IDataReader reader = textCommand.ExecuteReader() )
                    {
                        while ( reader.Read() )
                        {
                            Console.WriteLine(reader["Text1"] + " " + reader["Text2"]);
                        }
                    }
                }
            }
        }
    }
}

Results

In both cases the calls are made using RPC.

Here's what the trace reveals using CommandType.Text:

exec sp_executesql N'dbo.Test',N'@Text1 nvarchar(5),@Text2 nvarchar(5)',@Text1=N'Text1',@Text2=N'Text2'

And here is the result using CommandType.StoredProcedure:

exec dbo.Test @Text1=N'Text1',@Text2=N'Text2'

As you can see the text-call is wrapped in a call to sp_executesql so that it is properly parameterized. This will of course create a slight overhead, and thus my previous statement that using CommandType.StoredProcedure is faster still stands.

Another noteworthy thing, and which is also kind of a deal breaker here, is that when I created the procedure without default values I got the following error:

Msg 201, Level 16, State 4, Procedure Test, Line 0 Procedure or function 'Test' expects parameter '@Text1', which was not supplied.

The reason for this is how the call to sp_executesql is created, as you can see the parameters are declared and initialized, but they are not used. For the call to work, it should have looked like this:

exec sp_executesql N'dbo.Test @Text1, @Text2',N'@Text1 nvarchar(5),@Text2 nvarchar(5)',@Text1=N'Text1',@Text2=N'Text2'

Meaning, when you're using CommandType.Text you have to add the parameters to the CommandText unless you always want the default values to be used.

So, to answer your question

1. Using CommandType.StoredProcedure is faster.
2. If you're using CommandType.Text, then you'll have to add the parameter names to the call to the procedure unless you want the default values to be used.

There is actually a huge difference. If you specify the command type StoredProcedure then any parameter you add to your SqlCommand will be a parameter added the procedure call. If you leave it as Text then the parameters will be added to the batch, not to the procedure. To illustrate the point, lets create a dummy procedure:

create procedure usp_test 
    @p1 char(10)  = 'foo',
    @p2 int = 42
as
    select @p1, @p2;    
go

Then compile this tiny C# application:

   static void Main(string[] args)
    {
        ExecWithType(CommandType.Text);
        ExecWithType(CommandType.StoredProcedure);
    }

    static void ExecWithType(CommandType type)
    {
        using (SqlConnection conn = new SqlConnection(Settings.Default.connString))
        {
            conn.Open();
            using (SqlCommand cmd1 = new SqlCommand("usp_test", conn))
            {
                cmd1.CommandType = type;
                cmd1.Parameters.AddWithValue("@p1", "bar");
                cmd1.Parameters.AddWithValue("@p2", 24);
                using (SqlDataReader rdr = cmd1.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine("Type: {0} Result: @p1: {1} @p2: {2}", type, rdr[0], rdr[1]);
                    }
                }
            }
        }
    }

The result is:

Type: Text Result: @p1: foo        @p2: 42
Type: StoredProcedure Result: @p1: bar        @p2: 24

Ouch! For the CommandType.Text setting although the parameters were passed to the batch, they were not passed to the procedure. Source of many hours of debugging fun...