Executing stored procedures

Discussion in 'ASP General' started by Eugene Anthony, Jul 14, 2005.

  1. 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 ***
     
    Eugene Anthony, Jul 14, 2005
    #1
    1. Advertising

  2. 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/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
    --
    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.
     
    Bob Barrows [MVP], Jul 14, 2005
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Taras
    Replies:
    2
    Views:
    4,825
    Rick Spiewak
    Oct 5, 2003
  2. Soumitra Banerjee

    Putting stored procedures in a dll

    Soumitra Banerjee, Feb 27, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    412
    Soumitra Banerjee
    Feb 27, 2004
  3. lhak
    Replies:
    1
    Views:
    5,455
    √Čric Moreau [VB MVP]
    Oct 23, 2004
  4. mono
    Replies:
    1
    Views:
    334
    David Waz...
    Jul 4, 2003
  5. Matt

    Waiting on Stored Procedures

    Matt, Jul 18, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    375
    Gary Varga
    Jul 18, 2003
Loading...

Share This Page