Get the count of rows in a stored procedure result regardless of number/type of columns

Question

I have developed the following code:

  CREATE PROCEDURE [dbo].[Test01]
  AS
  BEGIN
    SELECT * FROM TestTable
  END


  CREATE PROCEDURE [dbo].[Test02]
  AS
  BEGIN
    DECLARE @tmp TABLE
    (
      TestID int,
      Test   nvarchar(100),
    )
    INSERT INTO @tmp
    EXEC Test01
    SELECT COUNT(*) FROM @tmp
  END

But if I add or remove a column on TestTable I must to modify @tmp otherwise the result is:

Column name or number of supplied values does not match table definition

How can I solve this problem?

This is possible via [`OPENQUERY`/`OPENROWSET`](http://stackoverflow.com/questions/3005361/how-to-query-on-table-returned-by-stored-procedure-within-a-procedure/3005435#3005435) but seems highly inefficient. If you need a query to get the count of something you are better off writing that particular query. Also if you rewrite your stored proc as an inline TVF then you can just call `COUNT(*)` on that and that should be OK efficiency wise. — Martin Smith, Oct 02 '12 at 11:08
Sorry I simplified too without explaining the problem: the first SP performs the JOIN to give me a result, the second SP, inserts rows only if the number of results in the first SP is equal to a parameter passed. Anyway thank you very much, I solved the problem with OPENROWSET. Damiano — Blackshark, Oct 02 '12 at 12:52

score 2 · Accepted Answer · answered Oct 02 '12 at 11:21

2

Try specify the columns manually:

SELECT a, b FROM TestTable

and

INSERT INTO @tmp (a, b)

This should fix the error you've mentioned.

answered Oct 02 '12 at 11:21

abatishchev

98,240
88
296
433

score 0 · Answer 2 · answered Oct 02 '12 at 11:14

My first comment would be that SELECT * is frowned upon unless you actually know what you are doing. I would strongly advise against it in your particular use case, precisely because it can get you into the trouble you have identified.

In your particular case, for the specific SP written, you have not used SET NOCOUNT ON, so you can retrieve the count using

SELECT @@ROWCOUNT

But then as Martin has commented, that's just some sample code you threw together. Otherwise, why even use 2 SPs.

Get the count of rows in a stored procedure result regardless of number/type of columns

2 Answers2