Stored Procs

[@ HOTMAIL DOT COM]

What are the different ways to fire stored procedures in a SQL 2000
server? Which is better? I have seen simply:

set rs = conn.execute("sproc")

and I have also seen massive command.parameter ... bla bla ...

[Foo Man Chew]

> What are the different ways to fire stored procedures in a SQL 2000

> server? Which is better?

Depends on how you define "better"...

[@ HOTMAIL DOT COM]

Faster. Most efficient. Or even just why one is used instead of the other.

Foo Man Chew wrote:

>>What are the different ways to fire stored procedures in a SQL 2000
>>server? Which is better?

>
>
> Depends on how you define "better"...
>
>

[@ HOTMAIL DOT COM]

Faster. Most efficient. Or even just why one is used instead of the other.

Foo Man Chew wrote:

>>What are the different ways to fire stored procedures in a SQL 2000
>>server? Which is better?

>
>
> Depends on how you define "better"...
>
>

[Bob Barrows]

@ HOTMAIL DOT COM" <""dk_now_48\"@ HOTMAIL DOT COM wrote:

> What are the different ways to fire stored procedures in a SQL 2000
> server? Which is better? I have seen simply:
>
> set rs = conn.execute("sproc")
>
> and I have also seen massive command.parameter ... bla bla ...

There are several alternatives.

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

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
[url]www.sqlsecurity.com[/url])

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:
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=[/url]
&a=clear

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.

[Foo Man Chew]

> 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.

Can you elaborate? Maybe show some benchmarks?

[Bob Barrows]

Foo Man Chew wrote:

>> 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.

>
> Can you elaborate? Maybe show some benchmarks?

If you use SQL Profiler to trace your server activity during the execution
of a procedure, you will see the intervention of several system procedures,
sp_preparesql, etc., when using the dynamic sql and explicit Command object
techniques, that you will not see when using the
"procedure-as-connection-method" technique. In his book "ADO Examples and
Best Practices", Bill Vaughn reported a substantial improvement in
performance. This has not been borne out by my own (and others') testing: I
saw a slight improvement. So, I will say tha it does not make a great deal
of difference, but if you're trying to squeeze the last millisecond of
performance out of your app ...

Many people do not like this technique because they liken it to
late-binding, which can be a performance killer in VB. However, in vbscript,
late-binding is all we do (all variables being variants ... ).

Another factor to consider is that vbscript is not a compiled language. This
means that if you have a lot of parameters, it may take more time to compile
and execute a bunch of CreateParameter statements than it would to use
Parameters.Refresh even. YMMV

Bob Barrows

--
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"