Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Unanswered: Number of Rows returned by sp

    Is it possible to get the number of Rows returned by a stored procedure in a profiler ?
    if yes, what parameters shud I be looking for?

    THanks

    Harshal

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you mean like:

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 @rs int OUTPUT
    AS
    SELECT * FROM Orders
    SELECT @rs = @@ROWCOUNT
    GO
    
    DECLARE @rs int
    
    EXEC mySproc99 @rs OUTPUT
    
    SELECT @rs
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    no.
    the sps are not to be changed.
    its like

    create procedure test
    as
    begin
    select * from table
    end

    suppose this is the existing sp which is being used by a application
    I want to put the profiler to get the number of rows which are being selected by this sp.
    since its the prod db I cant change the sp.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You may want to look into sp_trace_generateevent and related topics, but I think you'd still need to alter your procedures.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    From books online...

    Use the SPtmtCompleted event, and trace the Integer Data.

    It is a bit difficult to find in BOL, so I bookmarked it, for myself. Try searching on "Monitoring with SQL Profiler Event Categories" The quoted string, will get you the desired result

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can find the topic, and I can find a table that shows that the integer counter returns something for a stored procedure's StmtCompleted event, but darned if I can find anywhere that it explicitly says what that integer is!

    Good sleuthing!

    -PatP

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Pat: There should be two links on the page. Following "Stored Procedures Data Columns" should bring you to a short description of all the data elements.

  8. #8
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Hey thank you guys !!
    I got the required data by importing the required data to my box and altered the proc and ran it. Ok I agree this is not a professional way to do things but it was a show stopper bug in the system so had to find a way.
    I'll check the profiler events that rdjabarov and MCrowley has suggested.

    Thanks again.

    Regards,
    Harshal.

Posting Permissions

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