Professional Web Applications Themes

SP returned recordsets - Microsoft SQL / MS SQL Server

Hi all, I have a stored procedure which has a number of select and insert statements in it. It is used to insert records in a number of tables. The last select statement returns a single row, which I want to use as a kind of returned parameter. I call this stored procedure from some ASP code using OLE DB, but this is largely irrelevant I think. The problem is that the stored procedure seems to return the FIRST select statements results as it's recordset, not the LAST within the stored procedure. Is there a way around this? Can you ...

  1. #1

    Default SP returned recordsets

    Hi all,

    I have a stored procedure which has a number of select and insert statements
    in it. It is used to insert records in a number of tables. The last select
    statement returns a single row, which I want to use as a kind of returned
    parameter. I call this stored procedure from some ASP code using OLE DB, but
    this is largely irrelevant I think.

    The problem is that the stored procedure seems to return the FIRST select
    statements results as it's recordset, not the LAST within the stored
    procedure. Is there a way around this? Can you "Clear" (or turn off) the
    stored procedures output just before running the last select? Is there some
    other way?

    Thanks for all and any help.

    Regards,
    Saul


    Saul Guest

  2. #2

    Default Re: SP returned recordsets

    Hi Saul,

    If you never need the output of the all the select statements except the
    last one, then you should remove all those select statements from the stored
    procedure. This will improve the performance of your SP.

    If it is not possible , then from the ASP page you can use the NextRecordset
    method of ADO recordset to return the results of the next command statement
    or of a stored procedure that returns multiple results.


    Praveen
    MCDBA , MCSD

    "Saul Bryan" <net.au> wrote in message
    news:oc7Qa.2682$bigpond.net.au... 
    statements 
    but 
    some 


    praveen Guest

  3. #3

    Default Re: SP returned recordsets

    A stored procedure can return multiple result sets to the client. You can
    get the result set you want by iterating through the appropriate collection
    (Recordset.NextRecordset in ADO).

    If you are only interested in the last resultset, why do you have the select
    statement in the stored procedure that return the other resultsets as well?
    They are not necessary then.


    "Saul Bryan" <net.au> wrote in message
    news:oc7Qa.2682$bigpond.net.au... 
    statements 
    but 
    some 


    Jacco Guest

  4. #4

    Default Re: SP returned recordsets

    Saul
    If you have to SELECT statements in your SP on the client will be return
    the first one
    Is this your situation? Am I right?

    So, you can do that by re-wtite it with a logic condition.
    I mean IF something
    do.... this select
    ELSE
    do ... other one.

    the second way you can use it to divide you SP into two SP and call thme
    from the client respectively.





    "Saul Bryan" <net.au> wrote in message
    news:oc7Qa.2682$bigpond.net.au... 
    statements 
    but 
    some 


    Uri Guest

  5. #5

    Default Re: SP returned recordsets

    Far out!

    Thanks Praveen, Jacco and Uri for such fast responses! Legends.

    I was not aware of the Recordset.NextRecordset method. This certainly sounds
    like it could help me. I will try it tomorrow when I am at work.

    I would like to spend a moment explaining the situation a bit more clearly -
    perhaps you guys have some more words of wisdom.

    In summary, this is the problem: I want to use a stored procedure to insert
    a record, and return the primary_key value of the new record. The values to
    insert are passed as parameters. The returned key is used by the web
    application to allow re-editing of the record immediately after saving it.

    For example the Stores table has a store record to be inserted. The table
    does not have an identity (and I can't change the table design because of
    legacy systems), and so the first thing I need to do is find the max of the
    primary key and assign this (+1) to a temporary variable. This involves a
    select statement. I'm not at the code at the moment, otherwise I'd give you
    the sample, but it asigns the result of the select to a variable, like
    new_StoreID. This is the recordset I think the stored procedure is
    returning, and it's empty (but it does assign the value to the variable
    correctly, which seems a bit like a contradiction). Then I do the insert,
    using the new_StoreID. Then I wanted to return the value of the StoreID, so
    I do something like

    ' Return the new key as a recordset.
    SELECT 'Store_ID' as Key_name, new_StoreID as Key_Value

    This needs to be genericly named, as I want to do the same thing for SP's in
    the application (eg Product inserts), but have it all handled the same in
    the ASP code which processes this returned recordset. Note that this SELECT
    does not hit any tables, it's more like a print than anything. This is
    legitimate and legal, I believe!

    When I run this SP in QA, I get the messages "1 record(s) affected" 3 times.
    Once for the MAX select, once for the insert, and once for the return select
    above. The results grid shows only the above select results, eg..

    Key_Name | Key_Value
    ==================
    Store_ID | 12345

    This is the only table returned visible in QA. This is why I was a bit
    confounded why it didn't work with my code. It told me the recordset was
    empty, but then I worked out it was somehow trying to get to the results of
    the first (MAX) select.

    Now, I will admit at this point that I may have missed something really
    obvious, or be going about this all the wrong way. I have a fairly good
    background in databases and programming, but I'm still learning how to write
    Stored Procedures and transact SQL. However, I think it's a fairly common
    problem - when you insert a new record in a table, how can you immediately
    get a handle on it?

    This problem is even harder with identities, you are forced to do a search
    (select) right after the insert, is this correct? Seems a bit silly. It
    would be nice to somehow access the record you just created. Perhaps others
    go about this differently - I'd be interested to hear if it's the case.

    For security, performance and other reasons, I don't want to do the INSERT
    directly in the ASP code, so please don't suggest that. I want to use SP's.

    Thanks again if you've made it this far, sorry if I rambled a bit!!

    Regards,
    Saul


    Saul Guest

  6. #6

    Default Re: SP returned recordsets

    Scary (but common) approach.
    - select var=max(col)+1 from table

    then assuming that no other thread/call is doing the same thing before you
    get to inserting the actual record.
    You either need to lock the table until you are done inserting (yikes) or
    use a different approach in obtaining unique ids. I'm in a rush so hoping
    somebody else here will provide some alternative approaches.

    Also split off this getuniqueid call to a seperate sp and call it from your
    original one. This will not only prevent getting multiple recrordsets as a
    result but also create a nice reusable sp.

    Edwin Kusters
    Hot ITem Informatica

    "Saul Bryan" <net.au> wrote in message
    news:%J9Qa.2846$bigpond.net.au... 
    sounds 
    clearly - 
    insert 
    to 
    the 
    you 
    so 
    in 
    SELECT 
    times. 
    select 
    of 
    write 
    others 
    SP's. 


    Edwin Guest

  7. #7

    Default Re: SP returned recordsets

    Saul Bryan (net.au) writes: 

    I'm not sure that I really follow, but if you issue a SET NOCOUNT ON
    you will not get the closed resultsets from the rowcounts of the INSERT
    statements.

    But in fact you procedure should not return any result set at all; you
    should pass the key of the newly inserted row as an OUTPUT parameter.
    Then you can use adEexecuteRecords for the Options parameter. There is
    a considerable performance gain of passing the key as parameter rather
    than a result set. For an occasional INSERT it does not make a difference,
    but if you are inserting 3000 rows in one storm, you would notice the
    difference.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. ASP Recordsets
    By GTN1707 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 4
    Last Post: October 28th, 03:14 PM
  2. ADO recordsets on the client
    By Donald in forum ASP Database
    Replies: 2
    Last Post: June 28th, 08:35 PM
  3. Replies: 0
    Last Post: January 13th, 11:03 PM
  4. paging recordsets
    By Andy in forum ASP Database
    Replies: 1
    Last Post: September 6th, 07:11 AM
  5. Do I have the right idea about using recordsets?
    By Dickie Black in forum Microsoft Access
    Replies: 2
    Last Post: July 18th, 02:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139