ADO stored proc

[Nick]

I am new to vbscript and need to learn it but more importantly I need to know
how to access a SQL Server stored procedure using vbscript and ADO. I've
looked at vbscript books and sql server books on Amazon and don't know what
to get that would discuss vbscript for a stored procedure. Could you please
direct me to a website or recommend a book or books that explain it.
thanks - nick

[Bob Barrows [MVP]]

This is my canned reply for this question. It was written in the context
of using vbscript with ASP, so if you are not using ASP, just remove
"Server." from any code snippets that I use:

There are several alternatives.

1. Use the technique described here:
http://www.aspfaq.com/show.asp?id=2201

Personally, I don't like this technique since:
a. You have to worry about preventing hackers from injecting SQL into
your code (there are ways to prevent this - see the SQL Injection FAQ at
www.sqlsecurity.com)

b. You have to correctly delimit your parameter values, just as if you
were creating a dynamic SQL statement (actually, that is exactly what
you are doing here). You also have to correctly handle string values
that contain literal characters that are normally used as delimiters.
While I've done this enough times so that it is second nature to me now,
in the beginning this was the largest stumbling block to my learning how
to create strings containing dynamic SQL statements.

c. There is some performance-impairing overhead involved with both the
concatenation of the SQL statement that ultimately runs the stored
procedure, and the preparation of the statement on the SQL Server box,
which happens before the statement is actually executed.

d. It forces you to return data only by recordsets: no output or return
values can be used with this technique. Recordsets require substantial
resources, both on the SQL Server which has to assemble the resultset
and pass it back to the client, and on the web server which has to
marshal the resultset and transform it into an ADO recordset. This is a
lot of overhead when we're talking about returning one or two values to
the client.

However, a lot of people do like this technique because:
a. They have no problem knowing when and how to concatenate delimiters
into the SQL statement, and how to handle string parameters that contain
literal characters that are normally used as delimiters
b. They have taken the necessary steps to prevent SQL Injection
c. You can assign the statement to a variable and, if there's an error
during the debug process, you can response.write the variable to see the
actual statement being sent to the SQL Server. If the statement has been
created correctly, you can copy and paste it from the browser window
into Query Analyzer and further debug it
d. They are aware of the performance hit, and consider it to be too
minor to worry about. (To be fair, in many cases, this perfomance hit is
relatively minor)

The alternatives I prefer completely eliminate objection b from above.

1. If you have output parameters, or you are interested in using the
Return value from your procedure, use an explicit ADO Command object.
Now, this can be tricky, especially if you do it the correct way
(manually create the Parameters collection using CreateParameter instead
of using Parameters.Refresh which involves an extra time-consuming trip
to the database). However, there are many stored procedure code
generators out there that vastly simplify this process, including the
one I wrote which is available here:

http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

2. The technique I use most often is the
"procedure-as-connection-method" technique. With ADO 2.5 and higher,
stored procedures can be called as if they were native methods of the
connection object, like this:
conn.MyProcedure parmval1,...,parmvalN
This completely avoids the need to worry about delimiters, literal or
otherwise. Plus it turns out that this technique also causes the
procedure to be executed in a very efficient manner on the SQL Server
box.

You can also use this technique if your procedure returns a recordset:
set rs=server.createobject("adodb.recordset")
'optionally, set the cursor location and type properties
conn.MyProcedure parmval1,...,parmvalN, rs

This technique does not work in ADO.Net, so if you are considering
porting to dotnet, then you should avoid this technique.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Nick]

Thanks Bob. That's a big help. I'll give it a shot.
Also, could you recommend a book - vbscript or sql server? - that would
provide general information on vbscript-ADO-sql server programming?
- nick

[Bob Barrows [MVP]]

There's nothing I know of that specifically covers that topic. Try doing a
search at amazon.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"