CODEWITHSUNDEEP
sqlt-sqlstored-procedures
Argyle Ghost
Argyle Ghost

Reputation: 151

Executing multiple procs in select statement to return row count

Here is what I am trying to do, get the number of rows that a stored proc returns (plus a column name that it has in the proc) Obviously this is Pseudo-Code

SELECT
(select col_name, Count(*) FROM stored_proc1)
(select col_name, Count(*) FROM stored_proc2)
(select col_name, Count(*) FROM stored_proc3)

To return 2 columns as such

col_name  |  row_count
----------------------
myCol1        3
myCol2        6
myCol3        8

Is there anyway to do this?

Upvotes: 0

Views: 214

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166486

Not sure what the col_name part will be but you might wish to look at @@ROWCOUNT

So something like

DECLARE @R1 INT
EXEC SP1
SET @R1 = @@ROWCOUNT
DECLARE @R2 INT
EXEC SP2
SET @R2 = @@ROWCOUNT

SELECT 'SP1' SP_Number, @R1
UNION ALL
SELECT 'SP2' SP_Number, @R2

Upvotes: 1

Related Questions