c# - Why should I use SqlCommand.CommandType = StoredProcedure? -

this question has answer here:

• when executing stored procedure, benefit of using commandtype.storedprocedure versus using commandtype.text? 4 answers

question: difference between using standard sqlcommand , sqlcommand.comandtype = storedprocedure?

since i'm not sure if parameters passed command object by name or by order, 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

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 parameter names , parameter order matter here? 

i don't understand why should second?

the first redundant step, forces second (but trivial) query-plan parsed, generated, cached , executed. offers great opportunity mess (for example) forgetting add parameters. need consider parameters in first passed by position (in inner tsql), where-as in second passed by name; name preferable here. likewise, if add new parameter ocmd.parameters have maintenance step of maintaining inner command - or risk introducing bugs, where-as in second example you don't need extra.

basically, first example has nothing @ positive, , lots of negative points.

---

re pass-by-name versus pass-by-position, feature of exec keyword in tsql. there 2 uses:

exec myproc 'abc', 123 

or

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

the first by-position; 'abc' passed first declared parameter of myproc, , 123 passed second declared parameter of myproc. additional parameters assume default values if have one.

the second by-name; 'abc' passed parameter of myproc called @foo, , 123 passed parameter of myproc called @bar. other parameters assume default values if have one.

so in 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.