CommandType.Text vs CommandType.StoredProcedure

Question

Is there any benefit to explicitly using the StoredProcedure CommandType as opposed to just using a Text Command? In other words, is

cmd = new SqlCommand("EXEC StoredProc(@p1, @p2)");
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

any worse than

cmd = new SqlCommand("StoredProc");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

EDIT: Fixed bad copy paste job (again). Also, the whole point of the question is for a data access class. I'd much rather be able to pass the stored proc name and parameters in one line as opposed to extra lines for each parameter.

Joel Coehoorn · Accepted Answer · 2015-02-05 19:50:30Z

9

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 and raiserror functions, and the .Net program will receive and log those. What we learned was that CommandType.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.

edited Feb 5, 2015 at 19:50

answered Mar 13, 2009 at 18:19

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Lurker Indeed Over a year ago

Any examples on the implicit typing issue? I've heard that one a few times, but never seen it.

Joel Coehoorn Over a year ago

Say you have an index on a varchar column. .Net will assume nvarchar and not be able to use the index.

Lurker Indeed Over a year ago

Following that...so if I have a stored proc with a varchar argument, and I pass a string in, what happens?

Joel Coehoorn Over a year ago

It's less of an impact for SPs than for adhoc queries. SQL Server will just do an extra conversion from nvarchar to varchar once, rather than scrap a whole index. But it's still bad. Take datetime: if .Net gets a datetime column wrong and sql server can't implicitly convert, you've got a problem.

Joel Coehoorn Over a year ago

[continued] I haven't personally seen that last example because I always use explicit types on my query parameters, but I know it's possible.

|

Rohit Vipin Mathews · Accepted Answer · 2015-06-22 06:39:55Z

4

It's cleaner.

You're calling a stored procedure, why not just use the CommandType.StoredProcedure?

edited Jun 22, 2015 at 6:39

Rohit Vipin Mathews

11.8k15 gold badges60 silver badges113 bronze badges

answered Mar 13, 2009 at 18:02

Jesse Weigert

4,8945 gold badges31 silver badges37 bronze badges

2 Comments

Lurker Indeed Over a year ago

Because I'm writing a DB layer that allows using a parameter array for arguments.

marc_s Over a year ago

+1 - CLARITY is the magic word for any long-term project which needs to be concertned with things like maintainability! :)

Collectives™ on Stack Overflow

CommandType.Text vs CommandType.StoredProcedure

2 Answers 2

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related