Calling Stored Procedures

Calling Stored Procedures

Post by Erwin Pant » Sat, 19 Mar 2005 07:32:38


Hi,

I know in .NET in order to call a stored procedure passing parameters, you
have to add parameters to the SqlParameter collection. I want to know if
there is a way to call stored procedures in this format:

Command.Execute StoreProc Par1, Par2, Par3, Par4, Par5

This is how I was doing it in VB6. I know that adding to the SqlParameters
collection is a more "clean" way of doing it, but I find the above method
easier for debugging purposes. When I test the stored procedure, all I
have to do is highlight my parameters, bring them to my immediate window,
and voil?, I have my parameters that I can copy and paste them in Query
Analyzer to test out my stored procedure.

Any ideas ?

Thanx

E

--
Message posted via http://www.yqcomputer.com/
 
 
 

Calling Stored Procedures

Post by blackFores » Sat, 19 Mar 2005 17:08:49

Well I really recommend using parameters but I suppose you can set the
commandtype to Text

command.CommandType = CommandType.Text;

and then put your stored procedure call in the command text

command.CommandText = "StoreProc Par1, Par2, Par3, Par4, Par5";
command.Execute

 
 
 

Calling Stored Procedures

Post by Marc Scheu » Sat, 19 Mar 2005 22:11:42

>I know in .NET in order to call a stored procedure passing parameters, you

Yes, but it's not recommended - you should really use the SqlCommand
with the CommandType = StoredProcedure, and fill parameters.

Marc
================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch
 
 
 

Calling Stored Procedures

Post by Erwin Pant » Sat, 19 Mar 2005 23:16:39

Hi,

Like I said, using the SqlParameters collection is a more "clean" way of
programming, and you don't have to worry about the order of parameters, but
the way that I want to do it makes it easier for debugging purposes. Mind
you there is nothing stopping me from using the SqlParameter collection and
I can always comment my method just to highlight my parameters in debug
mode, get all the values from the immediate window, and test in Query
Analyzer.

Thanx for your help !

E

--
Message posted via http://www.yqcomputer.com/
 
 
 

Calling Stored Procedures

Post by Marc Scheu » Tue, 22 Mar 2005 16:34:16

>Like I said, using the SqlParameters collection is a more "clean" way of

and the more recommended and the more performant, too - it's just a
matter of fact that using the CommandType = StoredProc results in a
more efficient call than calling the SP through a "EXEC <spname>"
using a CommandType = Text.

Marc
================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch
 
 
 

Calling Stored Procedures

Post by Erwin Pant » Tue, 22 Mar 2005 23:24:58

Hi,

When developping an app, performance is key to me and my colleagues, so I
would have to convince them that this way actually "performs" better. So
how much really of a better performance do you get by using CommandType =
StoredProc vs. "EXEC <spname>" using a CommandType = Text ? Is it just
because CommandType = Text must parse the text in order for it to call the
stored procedure ? Either way, both methods are still calling the already
compiled stored procedure so I would only see a slight drop in performance
when CommandType = Text must be parsed. I guess the best way would be to
actually do a performance test but if you have any more input on this
subject, it would be greatly appreciated.

Thanx

E

--
Message posted via http://www.yqcomputer.com/
 
 
 

Calling Stored Procedures

Post by Marc Scheu » Wed, 23 Mar 2005 16:13:40

>When developping an app, performance is key to me and my colleagues, so I

Amongst other things (from the "ADO.NET Best Practices" document by
Microsoft,
http://www.yqcomputer.com/ )

"Best Practices with SqlCommand

A quick tip for executing stored procedures using the SqlCommand: If
you are calling a stored procedure, specify a CommandType of
StoredProcedure for the CommandType property of the SqlCommand. This
removes the need to parse the command before execution, by explicitly
identifying it as a stored procedure."

Also, as mentioned by SQL Server architect Gert Drapers at TechEd
2004, using the "Execute <sproc name>" way of doing things is a so
called "Text Event", which is inherently less efficient than when
using the CommandType = StoredProc, in which case the stored proc is
called using a RPC (remote procedure call) event.

I can't quantify in terms of milliseconds or % CPU Load, but since
both a best practices document describes it, and SQL Server architects
recommend it, I guess there's a good reason why you should do it that
way! ;-)

Marc

================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch
 
 
 

Calling Stored Procedures

Post by Erwin Pant » Thu, 24 Mar 2005 01:46:57

I am still curious to see however exactly how efficient and performing it
is between the two. Because yes I agree that a program should be written
with efficient code, except let's say that you want to test a Stored
Procedure in Query Analyzer, what would be the fastest way for you to get
the values of your parameters ? At least if you had Par1 + Par2 + Par3 +
Par4 + Par5, all you have to do is highlight your code, copy it, paste it
in the immediate window and BAM ! you have the results of your parameters
which you can copy and paste it in Query Analyzer. With the "more
effecient" way, you would have to get each and every value of your
parameters one at a time. I just see my way a more convenient way to get
the values of my parameters quick and easy. I know it's less efficient,
but to what point ? A performance test would do. For fun, I will run a
performance test calling a stored procedure like 1,000 times using both
methods and see what happens ! I'll keep you posted.

E

--
Message posted via http://www.yqcomputer.com/
 
 
 

Calling Stored Procedures

Post by JSantor » Fri, 08 Apr 2005 14:11:19

I just happened to do a test like this the other day...
* we wanted to return all columns of one row using straight sql, so we
passed in "select * from customer where key = 123", it took .03
seconds.
* then we put the select inside a stored proc, called the proc, passed
in the 123 argument, got the row back in 0.00 seconds.
To do this right we should have executed both thousands of times, but
life is short.
Yes, its faster, but I sure hate having logic spread out everywhere.
Now I have 50,000 lines of VB code, Plus 200 stored proc names to keep
in my head.