Why should I use SqlCommand.CommandType = StoredProcedure? [duplicate]

Question

Question: What is the difference between using a standard SQLCommand and SQLCommand.ComandType = StoredProcedure?

Since I'm not sure if parameters are passed to the command object by name or by order, I prefer this:

SqlCommand oCmd = new SqlCommand("exec sp_StoredProcedure @Param1, @Param2, @Param3", oDBConnection);
oCmd.Parameters.Add("Param1", SqlDbType.Bit).Value = var_param1;
oCmd.Parameters.Add("Param2", SqlDbType.NVarChar).Value = var_param2;
oCmd.Parameters.Add("Param3", SqlDbType.NVarChar).Value = var_param3;

rather than

SqlCommand oCmd = new SqlCommand("sp_StoredProcedure", oDBConnection);
oCmd.CommandType = StoredProcedure;
oCmd.Parameters.Add("Param1", SqlDbType.Bit).Value = var_param1;
oCmd.Parameters.Add("Param2", SqlDbType.NVarChar).Value = var_param2;
oCmd.Parameters.Add("Param3", SqlDbType.NVarChar).Value = var_param3;
//Do the parameter names and the parameter order matter here?

I don't understand why I should do the second?

Answer 1

The first is a completely redundant step, that forces a second (but trivial) query-plan to be parsed, generated, cached and executed. It also offers great opportunity to mess up by (for example) forgetting to add the parameters. You also need to consider that the parameters in the first are now passed by position (in the inner TSQL), where-as in the second they are passed by name; by name is usually preferable here. Likewise, if you add a new parameter to oCmd.Parameters you now have an extra maintenance step of maintaining the inner command - or risk introducing bugs, where-as in the second example you don't need to do anything extra.

Basically, the first example has nothing at all positive, and lots of negative points.

---

Re pass-by-name versus pass-by-position, this is basically a feature of the exec keyword in TSQL. There are two uses:

exec MyProc 'abc', 123

or

exec MyProc @foo='abc', @bar=123

The first is by-position; 'abc' is passed to the first declared parameter of MyProc, and 123 is passed to the second declared parameter of MyProc. Any additional parameters assume their default values if they have one.

The second is by-name; 'abc' is passed to the parameter of MyProc called @foo, and 123 is passed to the parameter of MyProc called @bar. Any other parameters assume their default values if they have one.

So in your specific example:

exec sp_StoredProcedure @Param1, @Param2, @Param3

is pass-by-position, and:

exec sp_StoredProcedure @Param1=@Param1, @Param2=@Param2, @Param3=@Param3

is bass-by-name.