Executing stored procedures

E

Eugene Anthony

Method 1:

set rs = Server.CreateObject("ADODB.Recordset")
objConn.usp_RetrieveCategories rs

Method 2:

set rs = objConn.Execute("usp_RetriveCategories")

Which method is considered to efficient. Is it method 1 or method 2?.
The stored procedures returns no values.

Your help is kindly appreciated.

Regards

Eugene Anthony
 
B

Bob Barrows [MVP]

Eugene said:
Method 1:

set rs = Server.CreateObject("ADODB.Recordset")
objConn.usp_RetrieveCategories rs

Method 2:

set rs = objConn.Execute("usp_RetriveCategories")

Which method is considered to efficient. Is it method 1 or method 2?.
The stored procedures returns no values.

If you mean that the procedure returns no data in the form of a resultset,
then the answer is: neither. Don't create a recordset to run a prodcedure
that is not returning records.Do this to execute a procedure that returns no
records:

objConn.usp_RetrieveCategories
or
objConn.Execute "usp_RetriveCategories",,129

No recordset involved. My preference is the first one.

If the recordset does return records, then Method 1 is slightly more
efficient, which you can see for yourself by using SQL Profiler. Since
method 2 is in effect, using dynamic sql to execute a stored procedure, SQL
Server must do a little more processing to handle it. You can see that extra
processing using Profiler.

Now, that extra processing is unlikely to make any difference to your
application's performance. To me, efficiency is not the deciding factor when
choosing between these techniques.

The biggest downside to Method 2, IMO, is when you need to pass parameters
to the procedure. Using dynamic sql forces you to do
1. concatenation - string-handling was never one of vbscript's strong
points.
2. extra processing to the data to handle embedded delimiters, and more
importantly, to prevent hackers from using sql injection to compromise your
database and site. Given that new techniques to inject sql are constantly
being discovered, it will become increasingly difficult to filter out these
exploits while still being able to pass real data. Using parameters instead
of dynamic sql stops sql injection (unless you use dynamic sql in your
stored procedure of course).

Here is some more info about dynamic sql:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

What people like about Method 2 is that it gives them the ability to
Response.Write the statement to allow it to be debugged:

SQL="EXEC SomeProcedure " & ...
response.write SQL

Writing it to Response allows the resulting statement to be copied from the
browser window and pasted into QA to be tested.

Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Arrays 0
Error: Object is closed 1
fill combo box with data from database 4
combo box 0
session 1
asp and ms sql 10
Automatic parking area lighting system 4
error handling 4

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top