resources - stored procedure

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

  1. I have created the following stored procedure whereby it will check
    whether the categoryID is valid and if it is then the updation will be
    performed, else a -1 will be returned. This procedure will be execute
    with asp. Is this considered to be efficient?.


    create procedure usp_updateCategories
    @CategoryID int
    @CategoryDescription varchar(30),
    @ParentCategoryID int
    AS SET NOCOUNT ON

    if exists(SELECT top 1* FROM categories WHERE CategoryID=@CategoryID)
    begin
    UPDATE categories set
    CategoryDescription=@CategoryDescription,ParentCategoryID=@ParentCategor
    yID WHERE CategoryID=@CategoryID
    Select 0
    end
    else
    Select -1

    Return
    GO


    Regards

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jul 15, 2005
    #1
    1. Advertising

  2. Eugene Anthony

    CJM Guest

    "Eugene Anthony" <> wrote in message
    news:%...
    >I have created the following stored procedure whereby it will check
    > whether the categoryID is valid and if it is then the updation will be
    > performed, else a -1 will be returned. This procedure will be execute
    > with asp. Is this considered to be efficient?.
    >
    >
    > create procedure usp_updateCategories
    > @CategoryID int
    > @CategoryDescription varchar(30),
    > @ParentCategoryID int
    > AS SET NOCOUNT ON
    >
    > if exists(SELECT top 1* FROM categories WHERE CategoryID=@CategoryID)
    > begin
    > UPDATE categories set
    > CategoryDescription=@CategoryDescription,ParentCategoryID=@ParentCategor
    > yID WHERE CategoryID=@CategoryID
    > Select 0
    > end
    > else
    > Select -1
    >
    > Return
    > GO
    >
    >


    Eugene,

    This should really be posted in one of the SQL server groups. [Follow-ups
    set to m.p.sqlserver.programming]

    There is nothing particularly wrong with your implementation; AFAIK it's not
    an absolute howler. You may or may not be able to improve on it, on the
    other hand, SQL Server will know what you are trying to achieve and will
    optimise the query accordingly when it works out the execution plan. The key
    is to test this implementation against and others you can think of in Query
    Analyzer - see which costs the most.

    Chris
     
    CJM, Jul 15, 2005
    #2
    1. Advertising

  3. CJM wrote:
    >
    > This should really be posted in one of the SQL server groups.


    I disagree. it's relevant here (asp.general), because the way the procedure
    is written effects the way the client (asp) will interact with the database.

    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"
     
    Bob Barrows [MVP], Jul 15, 2005
    #3
  4. Eugene Anthony wrote:
    > I have created the following stored procedure whereby it will check
    > whether the categoryID is valid and if it is then the updation will be
    > performed, else a -1 will be returned. This procedure will be execute
    > with asp. Is this considered to be efficient?.
    >
    >
    > create procedure usp_updateCategories
    > @CategoryID int
    > @CategoryDescription varchar(30),
    > @ParentCategoryID int
    > AS SET NOCOUNT ON
    >
    > if exists(SELECT top 1* FROM categories WHERE CategoryID=@CategoryID)
    > begin
    > UPDATE categories set
    > CategoryDescription=@CategoryDescription,ParentCategoryID=@ParentCategor
    > yID WHERE CategoryID=@CategoryID


    Fine up to here ...

    > Select 0
    > end
    > else
    > Select -1
    >


    Although some may argue, I would use a Return parameter for this rather than
    returning a bulky resultset. It makes no sense to return a cursor just to
    pass a single value back to the client. To get an idea how much extra data
    is sent across the wire when you do this, use the recordset's Save method
    after you execute this procedure to save the recordset to an xml file. Since
    the xml file is simply text, you will be able to open it in Notepad and see
    all the extra stuff that had to be passed along with your single integer
    value. To me, using a parameter (Return or output) makes much more sense.
    When a parameter is used, the only data sent back to the client over the
    network is the value ... nothing else. When the ADO engine receives the
    value, it does not have to construct a recordset object and marshal the data
    into the cursor. All it has to do is set the parameter object's value to the
    value of the data that was returned from the database. You can't get more
    efficient than that.

    My criteria are:

    Use resultsets (Select statements) to return multiple records - you need a
    cursor in this type of situation, so you really have no alternative but to
    use a select statement to return a resultset.

    Use Return and Output parameters to return single values where you do not
    need the functionality of a cursor. This applies to the above procedure.

    Of course, using Return and output parameters (see
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/msg/2c935bd7c531d82b)
    makes it a little more difficult to write the vbscript code in asp ... so I
    have written a free code generator to make this task a little easier. You
    can get it here:
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

    You will have the source code, so you can customize it if you don't like the
    names I used for the variables in te generated code.

    Anyways, instead of:

    *******************
    Select 0
    end
    else
    Select -1

    Return
    ********************

    I would do this:

    **********************
    RETURN 0
    end
    else
    RETURN -1
    **********************

    In your asp page, use an explicit Command object to execute the procedure so
    you can retrieve the result of the Return parameter:

    Dim cmd, param, catid, catdesc,parcat, retval

    set and validate the catid,... variables, then open your
    objConn connection, then:

    Set cmd=server.CreateObject("ADODB.Command")
    With cmd
    .CommandType=adcmdstoredproc
    .CommandText = "usp_updateCategories"
    set .ActiveConnection=objConn
    set param = .createparameter("@RETURN_VALUE", _
    adInteger, adParamReturnValue, 0)
    .parameters.append param
    set param = .createparameter("@CategoryID", adInteger, _
    adParamInput, 0, catid)
    .parameters.append param
    set param = .createparameter("@CategoryDescription", _
    adVarChar, adParamInput, 30, catdesc)
    .parameters.append param
    set param = .createparameter("@ParentCategoryID", _
    adInteger, adParamInput, 0, parcat)
    .parameters.append param
    .execute ,,adexecutenorecords
    'read the return value here:
    retval = .Parameters("@RETURN_VALUE").value
    ' or, slichtly more efficiently:
    ' retval=.Parameters(0).value
    end with


    HTH,
    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"
     
    Bob Barrows [MVP], Jul 15, 2005
    #4
  5. Is having different ways of executing the stored procedures from asp
    considered to be acceptable in the industry or does it all have to be
    the same standard.

    Regards

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jul 19, 2005
    #5
  6. Eugene Anthony wrote:
    > Is having different ways of executing the stored procedures from asp
    > considered to be acceptable in the industry or does it all have to be
    > the same standard.
    >


    There's always been different ways, some better than others. I'm not sure I
    follow you.

    My practice is to use what I consider to be the best way depending on the
    specific situation. Others may have a different practice.

    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 19, 2005
    #6
    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. Sarmad Aljazrawi

    New Stored Procedure Template in .Net

    Sarmad Aljazrawi, Dec 16, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    539
    Sarmad Aljazrawi
    Dec 16, 2003
  2. ES
    Replies:
    2
    Views:
    1,533
    jgy3183
    Aug 20, 2004
  3. Mike P
    Replies:
    0
    Views:
    3,309
    Mike P
    Jun 19, 2006
  4. Russell E. Owen
    Replies:
    0
    Views:
    713
    Russell E. Owen
    Sep 8, 2006
  5. AlexWare
    Replies:
    2
    Views:
    761
    Paul Uiterlinden
    Oct 23, 2009
Loading...

Share This Page