SP and Command object

Posted on 2005-02-25
Medium Priority
Last Modified: 2006-11-17
Hi All, Just a few questions:

1.  Is the command object theonly way to execute a stored procedure for a SQL server database?

2.  What are the benefits of using the command object?

3.  What are the differences between the connection object and the command object
Question by:apresto
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
LVL 15

Assisted Solution

deighc earned 800 total points
ID: 13404099
> 1.  Is the command object theonly way to execute a stored procedure for a SQL server database?

You can also execute a stored procedure by calling the Open method of the recordset object ...

rsObj.Open "<sp name and parameter values>", <connection object>, <cursor type>, <lock type>, adCmdStoredProc ' ADO constant = 4

or by calling the the Execute method of the connection object

set rsObj = connObj.Execute("<sp name and parameter values>, , adCmdStoredProc)

> 2.  What are the benefits of using the command object?

The only time you really have to use a command object is when you want to access output parameters or return values from a stored procedure. If you have to do this then the using the command object is not so much a "benefit" as a necessity.

You could argue that another includes the fact the command object includes a nice clear API for calling paramterized queries. Not a benefit as such, but a "nice touch".

> 3.  What are the differences between the connection object and the command object

These two ADO objects represent 2 distinct pieces of functionality. The connection object represents the, er, connection to the database. It allows you specify which database you want to connect to, a connection timeout property and functionality for connect to and disconnecting from the database. A command object represents an executable command given to the database. It can specify input parameters, output parameters and return values, and it can return datasets into recordset objects. It operates 'over' a connection object.
LVL 15

Expert Comment

ID: 13404120
Hey, wait...

Is this homework ??

Expert Comment

ID: 13404156
Probably lol.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 23

Author Comment

ID: 13405391
No its not - i havent done homework for 2 years :o)

Its for a job i was offered, i start on monday and they asked if i knew the command object and stored procedures - i said "yes" thinking, how hard can it be - Im very familair with most other methods i just havent touched on these!
LVL 23

Author Comment

ID: 13405399
Thanks for the info deighc

Do you know of any good links or references to example of how command object is used and how getting output parameters is done etc? BAsically example of what you explained above?

Assisted Solution

fozylet earned 200 total points
ID: 13405791
LVL 23

Author Comment

ID: 13405894
Thanks fozylet,

Thats 2 out of three now guys, Kamoku-Wan if you give some useful information that could be a full house!

Ill have a look now thanks fozylet

Assisted Solution

Kamoku-Wan earned 200 total points
ID: 13406014
LVL 23

Author Comment

ID: 13406058
I meant something that hadnt been explained but a handy article anyway - The guys from rolla! How they helped me in my novice days!

Thanks guys

Im gonna have a look over the weekend and post back, Thanks
LVL 14

Accepted Solution

huji earned 800 total points
ID: 13423601
Hi there apresto.
I've asked related questions here in EE. Links:

Actually you are not supposed to read all of them. So I'm posting the conclusions. As I'm speaking based on that, I hope I'm not overlapping one other's post above.

1) Well there are ways to call a SP. Some methods are:
conn.Execute("exec SPname");
and my favorite method: to use a command object for it!

cmd.CommandText = "SPname"
cmd.CommandType = 4          'adCmdStoredProc

and if you have parameters, you can safely send them along with:

cmd.Parameters.Append cmd.CreateParameter("@colName",200,1,10,"lorem")
Where 200 stands for adVarChar and 1 stands for adParamInput

2) Benefits? I can count at least two:
(i)If you use a command object instead of a recordset object, then you can save your resources. Recordsets use more memory and CPU.
(ii)You can pass parameters in a very safe way, which is guaranteed not to be injected. (There is one silly exception: If the SP's code just creats another SQL statement and runs it!!) So you never need to check for single quotes, semicolons, etc.

3) As deighc said, the connection object, and the command object are two seperate things. Their names show this too! The connection object is needed when you want to connect to a DB, and a command/recordset/field/parameter/record property can do nothing to a DB without such a connection. On the other hand, after connecting to a database, you need to send your orders to it. The lazy method (which is open to injection) is to simply use conn.execute.

4)  Final thing:
The other great benefit in SPs:   The ASP programmer looks at the SP as a black box. He sends things to it as parameters, and recieve things in a recordset. The only thing he should know is the name of the table columns, name of the SP, and name of the desired parameters, and their types. So the SQL programmer can change the SQL part, without needing to modify a line of an ASP file. The other benefit is, when a single SQL command is going to be repeated in several ASP files, why not to have ONE instance of that code as an SP?

From the moment I learnt SP, I've never ran a code with direct SQL statements!

Wish I can help
LVL 23

Author Comment

ID: 13431614
Thanks everyone, thanks for the contribution Huji, helped out alot, Same with the rest of you!good info here for the EE search :o)!

Ciao for now

LVL 14

Expert Comment

ID: 13431703

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question