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
- Using
CommandType.StoredProcedure
is faster.
- 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.
Best Solution
One difference is how message pumping happens.
Where I used to work we had a number of batch processes that ran over night. Many of them simply involved running a stored procedure. We used to schedule these using sql server jobs, but moved away from it to instead call the procedures from a .Net program. This allowed us to keep all our scheduled tasks in one place, even the ones that had nothing to do with Sql Server.
It also allowed us to build better logging functionality into the .Net program that calls the procedures, so that the logging from all of the overnight processes was consistent. The stored procedures would use the sql
print
andraiserror
functions, and the .Net program will receive and log those. What we learned was thatCommandType.StoredProcedure
would always buffer these messages into batches of about 50. The .Net code wouldn't see any log events until the procedure finished or flushed the buffer, no matter what options you set on the connection or what you did in your sql.CommandType.Text
fixed this for us.As a side issue, I'd use explicit types with your query parameters. Letting .Net try to infer your parameter types can cause issues in some situations.