CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresdynamic-sql
Blackshark
Blackshark

Reputation: 45

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

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?

Upvotes: 2

Views: 1507

Answers (2)

abatishchev
abatishchev

Reputation: 100248

Try specify the columns manually:

SELECT a, b FROM TestTable

and

INSERT INTO @tmp (a, b)

This should fix the error you've mentioned.

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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.

Upvotes: 0

Related Questions