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.
