Inner Joining Results from multiple SP's

  • Using SQL Server 2K, I have a SP which calls 2 other SP's and returns a Name and a Record Count.

    Example:

    SP1 is Select 'Boy' as Name, Count(id) as C from T_Boys

    SP2 is Select 'Girl' as Name, Count(id) as C from T_Girls

    Is there a way to inner join the call to the 2 SP's so that I get one result set back instead of having to use the .nextrecordset in my ASP page.

    Some thing like..

    Exec usp_Boys

    INNER JOIN (maybe union here instead)

    Exec usp_Girls

    so that my results look like:

    Name | C

    Boys |1234567

    Girls|4567890

    Instead of like:

    Name | C

    Boys |1234567

    and

    Name | C

    Girls|4567890

    Peace Out!

    Regards,
    Matt

  • Could try using temp table

    create #tmp ([Name] varchar(10),C int)

    insert into #tmp exec SP1

    insert into #tmp exec SP2

    select * from #tmp

    Far away is close at hand in the images of elsewhere.
    Anon.

  • I think nextrecordset is a cleaner solution. Maybe it would make sense to build one more proc that duplicates the logic - usp_getallcounts or something like that.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Better still combine both into one single proc with selection param (eg Boys, Girls or Both).

    Far away is close at hand in the images of elsewhere.
    Anon.

  • My additional searches have pretty much agreed with Andy's recomendation.

    Thanks All.

    2nd question now. Is there a problem using system tables to return rowcounts instead of doing Select Count(*) from T_Name?

    The time saving is huge when I hit the systeme table (saves 30 seconds) as oppesed to the count.

    What's the downside of using the following?

    SELECT so.name, si.rows, Sort = case so.name

    when 'T_Boys' then 1

    when 'T_Girls' then 2

    else 0

    end

    FROM dbo.sysindexes si INNER JOIN dbo.sysobjects so

    ON si.id = so.id

    WHERE so.name in ('T_Boys', 'T_Girls')

    AND si.indid = 1

    Order by sort

    I had to use the join since I added a clustered index to the original table and it no longer appears in sysindexes as it's own record.

    Peace Out!

    Regards,
    Matt

  • Answer to your second question.

    Rowcount in system tables is not gauranteed to be accurate.

    I use it all the time though, as it is much faster.

    It is updated regularly, but not real time, so there could be more / fewer rows at any point in time.

    KlK, MCSE


    KlK

  • The tables are loaded weekly so there's no adding or deleteing records so I should be pretty safe. What happens if the table is dropped and recreated. Does the original reference still exist in the sysobjects table so there would now be 2 references to T_Boys or T_Girls?

    Peace Out!

    Regards,
    Matt

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

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