SP1 counts SP2 results

  • Hi everyone,

    I have a SP1 which returns a table result set. I want to create SP2 which returns an integer value for count(SP1)

    I can do this by creating a temporary table.

    Is there any way to achieve this without creating a temp table?

    Is there any table object in the tempdb that i can recall for SP1 reult set?

    Here is my code sample:

    --Lets create SP1

    CREATE PROC SP1

    AS

    SELECT au_lname

    FROM authors

    ---Lets create SP2 which counts SP1

    CREATE PROC SP2

    AS

    SET NOCOUNT ON

    CREATE TABLE #tmp

    (au_lname VARCHAR(20))

    INSERT #tmp (au_lname)

    EXEC SP1

    SELECT COUNT(*)

    FROM #tmp

    --Now we can execute SP2 to see how SP2 works

    EXEC SP2

  • --Lets create SP1

    CREATE PROC SP1

    @NoRows int OUTPUT

    AS

    begin

    set nocount on -- avoid unneeded network info

    SELECT au_lname

    FROM authors

    SET @NoRows = @@rowcount

    end

    go

    declare @Rowcount int

    exec sp1 @Rowcount output

    print @Rowcount

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi,

    Thank you for the reply.

    However, the application which calls those procedures need both of them explicitly.

    Which means,

    Application needs

    SP1 to get the result set

    SP2 to get how many of them

    Any more ideas?

    Thank you

  • it is actualy getting the resultset as well as the rowcount because that is returned as an outputvariable.

    .

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • >>However, the application which calls those procedures need both of them explicitly.

    By that, are you inferring that you have no control over the application code, and therefore can't make any changes to which procedures are called and what their parameters are ?

  • Day,

    ALZDBA was stating that the application only has to make one stored procedure call to retrieve both sets of information. The count is stored in output variable that the application can use.

    If you must have two sperate stored procedures, I think you are going to have to use a table variable or temp table for this.

  • Hi all,

    Application calls SP1 for result set

    and

    calls SP2 for how many results from SP1

    This application is kind of reporting tool. Even though two SP sounds they are related, they are consumed separately in the application. When application calls SP1, it only needs the table.

    I cannot change the application at this point. So, I guess I have to use that temp table.

    Thank you for all

  • Definitely you should change the App. It is a "bad" design to do that in two separated calls.


    * Noel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply