469,331 Members | 6,051 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

Executing stored procedures

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

*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #1
1 1514
Eugene Anthony wrote:
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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf
http://www.spidynamics.com/papers/SQ...WhitePaper.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
--
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.
Jul 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Tim Marshall | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.