TechnowledgeBase

You already know how to run SQL queries on a database using ADO and VbScript, but what about stored procedures? The reasons why you might want to use a stored procedure vs. a SQL query is beyond the scope of this article. The decision has been made, and now you just have to make it work. Let’s get started:

Declare some variables.


Dim cn 'ADO connection object
Dim cnCmd 'ADO Command object
Dim cnRecordset 'recordset returned from stored procedure
Dim cnParam 'ADO parameter passed to stored procedure
Dim strCS 'connection string
Dim boolError 'boolean is set to TRUE if an error occurs


Since we’ll be using an ADO connection, we might just need a connection string:


strCS="packet size=4096; integrated security=SSPI; data source=servername; persist security info=False;initial catalog=dbname; Provider=SQLOLEDB.1"


Now we open the connection:


Set cn = CreateObject("ADODB.Connection")
cn.Open strCS


Everything above this point was just creating a regular ADO connection. Now here’s where the fun part starts. Assuming everything above succeeded, we start by creating a COMMAND object


set cnCmd=CreateObject("ADODB.Command")


Now we need to set a few attributes (variables) on that command object.

Here they all are in one code block, with inline comments for explanations:


'Set the command object to use the ADO CONNECTION.
cnCmd.ActiveConnection = cn
'this means you are using a stored procedure.
cnCmd.CommandType = 4
'set the name of the stored procedure
cnCmd.CommandText = "sp_name_of_stored_procedure"
'this one means you can pass the parameters in any order,
'but you must identify them by name.
cnCmd.NamedParameters = True


Now we need to create the parameter object that we will use to pass parameters to the stored procedure. You can do it all with just one line, but that line requires some explanation. Here it is:


set cnParam=cnCmd.CreateParameter("@param",200,1,5,trim(ZIP))


And here’s what it means. The values I’m passing are as follows:

• ‘”@param” - name of paramater (in the stored procedure)
• ‘200 - data type of the parameter. “200″ is a varchar. Others are listed here.
• ‘1 - direction. 1=input, 2=output. I’m using 1 here because this single paramater is being passed TOO the procedure instead of being returned FROM it.
• ‘5 - length. Required for varchar data types. this example takes a 5-character string. If you pass something that is LONGER than this value, you will get an error.

Now we need to attach the parameter to the command object, and execute the stored procedure. In this example, I am creating an recordset object to store the results.


'this attaches the parameter to the COMMAND object
cnCmd.Parameters.Append cnParam
'execute the stored procedure.
'The result will be placed in the RecordSet object
Set cnRecordSet = cnCmd.Execute


And of course, at the very end you should always clean up after yourself.


cnRecordset.Close 'close recordset
set cnRecordset = nothing 'set recordset = nothing
cn.Close 'close connection
set cn=nothing 'set connection object = nothing
set cnCmd=nothing 'set command object = nothing


I didn’t go into a lot of details on the error checking, and in fact I left out most of the booleans and if statements that I would otherwise used to make the code a bit more bulletproof. I also left out what I did with the data in the recordset once I retrieved it. That will vary from application to application. The point was to show how to create and use an ADO Command to execute a SQL stored procedure, and I’ve done that. The rest is up to you.

Popularity: 29% [?]

This entry was posted on Tuesday, July 24th, 2007 at 7:07 am and is filed under VBScript. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.