March 6, 2003 at 1:46 pm
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
March 7, 2003 at 2:19 am
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.
March 7, 2003 at 6:17 am
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
March 7, 2003 at 6:30 am
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.
March 7, 2003 at 8:30 am
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
March 7, 2003 at 9:01 am
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
March 7, 2003 at 9:25 am
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