Best way to call a stored procedure

[mike]

H

There are two ways to execute a stored procedure using ADO.NE
- call "exec sp_myproc val1,val2" statement over SqlCommand myCommand = new SqlCommand(SQLQuery, mySqlConnection)
- use Command/Parameters objects to initialize and call the stored procedure

Which is a efficient way and wh

Appreciate your respons

Thank
Mike

 

[Rami Saad]

Hello,
Stored procedures are better. Statements are precompiled in the server, and
thus will be much faster when being executed.
Hope it's convincing.

Regards,
Rami Saad
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

 

[Simon Smith]

Hello,
Stored procedures are better. Statements are precompiled in the server, and
thus will be much faster when being executed.
Hope it's convincing.

Regards,
Rami Saad
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

Possibly faster, sometimes. Much faster? Nope.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx and various
related blogs and discussions such as: http://dotnetjunkies.com/WebLog/harpua/archive/2003/11/18/3676.aspx
And
http://weblogs.asp.net/rhoward/archive/2003/11/18/38298.aspx


The first link above quotes this from BOL:
<BOL>
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes
to statement processing that extend many of the performance benefits of
stored procedures to all SQL statements. SQL Server 2000 and SQL Server
7.0 do not save a partially compiled plan for stored procedures when they
are created. A stored procedure is compiled at execution time, like any
other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain
execution plans for all SQL statements in the procedure cache, not just
stored procedure execution plans. </BOL>

So I'm not convinced, I'm afraid