"Noel" <vb************@yahoo.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
I have two stored procedures
ALTER PROCEDURE dbo.qryCountOne
(@inputID int)
AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
(dbo.TableOne.value = @inputID)
ALTER PROCEDURE dbo.qryCountTwo
(@inputID int)
AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
(dbo.TableTwo.value = @inputID)
What would be the best way to combine these two, so that I only have to
make one database query, and the two values (CountOne, and CountTwo)
will get returned to me?
Any help\pointers greatly appreciated,
Noel
Output parameters are usually the best way to return scalar values from a
stored proc, so perhaps something like this?
create proc dbo.GetRowCounts
@TableOneID int
@TableOneCount int OUTPUT,
@TableTwoID int,
@TableTwoCount int OUTPUT
as
begin
select @TableOneCount = count(*)
from dbo.TableOne
where col = @TableOneID
select @TableTwoCount = count(*)
from dbo.TableTwo
where col = @TableTwoID
end
If you have to use a result set instead of output parameters, then see
"UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
MSSQL, so if that is the real column name, you might want to consider
changing it if possible - see "Reserved Keywords" in BOL.
Simon
