<p>In a stored procedure, I need to get the count of the results of another stored procedure. Specifically, I need to know if it returns any results, or an empty set.</p>

<p>I could create a temp table/table variable, exec the stored procedure into it, and then run a select count on that data.  But I really don't care about the data itself, all I need is the count (or presence/absence of data). I was wondering if there is a more efficient way of getting just that information.</p>

<p>I don't want to just copy the contents of the other stored procedure and rewrite it as a select count. The stored procedure changes too frequently for that to be workable.</p>

[{'answer_id': 79865, 'author': 'Nescio', 'author_id': 14484, 'author_profile': 'https://Stackoverflow.com/users/14484', 'pm_score': 1, 'selected': False, 'text': '<p>@@ROWCOUNT</p>\n'}, {'answer_id': 79866, 'author': 'Milan Babuškov', 'author_id': 14690, 'author_profile': 'https://Stackoverflow.com/users/14690', 'pm_score': 0, 'selected': False, 'text': '<p>If you can rewrite other procedure to be a simple function that returns a resultset, you can simply select count(*) from it.</p>\n'}, {'answer_id': 79870, 'author': 'Booji Boy', 'author_id': 1433, 'author_profile': 'https://Stackoverflow.com/users/1433', 'pm_score': 1, 'selected': False, 'text': '<p>use an out parameter</p>\n'}, {'answer_id': 79871, 'author': 'Matt Rogish', 'author_id': 2590, 'author_profile': 'https://Stackoverflow.com/users/2590', 'pm_score': 2, 'selected': False, 'text': '<p>Well, depending on how the stored procedures work, @@ROWCOUNT returns the # of results for ANYthing that SP will do (including updates):\n<a href="http://msdn.microsoft.com/en-us/library/ms187316.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms187316.aspx</a></p>\n\n<p>This will only work if the LAST thing you do in the sp is returning the rows to the client... Otherwise you\'re going to get the results of some other statement. Make sense?</p>\n'}, {'answer_id': 80006, 'author': 'Min', 'author_id': 14461, 'author_profile': 'https://Stackoverflow.com/users/14461', 'pm_score': 1, 'selected': False, 'text': '<p>I would think you could return the number of rows (using RETURN) or use an out parameter to get the value.</p>\n'}, {'answer_id': 80158, 'author': 'Ricardo C', 'author_id': 232589, 'author_profile': 'https://Stackoverflow.com/users/232589', 'pm_score': 0, 'selected': False, 'text': '<p>It seems that is someone else altering the other stored procedure and you need something to effectively check on the results no matter the changes to such procedure.</p>\n\n<p>Create a tempt table and insert the result from that procedure in it.</p>\n\n<p>Then you can perform a row count on the results. It is not the most efficient but most reliable solution if I understand correctly your problem.</p>\n\n<p>Snipet:</p>\n\n<pre><code>DECLARE @res AS TABLE (\n    [EmpID] [int] NOT NULL,\n    [EmpName] [varchar](30) NULL,\n    [MgrID] [int] NULL\n)\n\nINSERT @res \nEXEC dbo.ProcFoo\n\nSELECT COUNT(*) FROM @res\n</code></pre>\n'}, {'answer_id': 82603, 'author': 'Josef', 'author_id': 5581, 'author_profile': 'https://Stackoverflow.com/users/5581', 'pm_score': 0, 'selected': False, 'text': "<p>Given that you don't really need to know the count, merely whether there is or isn't data  for your sproc, I'd recommend something like this:</p>\n\n<pre><code>CREATE PROCEDURE subProcedure @param1, @param2, @Param3 tinyint OUTPUT\nAS\nBEGIN\n  IF EXISTS(SELECT * FROM table1 WHERE we have something to work with)\n   BEGIN\n    -- The body of your sproc\n    SET @Param3 = 1\n   END\n  ELSE\n   SET @Param3 = 0\nEND\n</code></pre>\n\n<p>Now you can execute the sproc and check the value of @Param3:</p>\n\n<pre><code>DECLARE @ThereWasData tinyint\nexec subProcedure 'foo', 'bar', @ThereWasData OUTPUT\nIF @ThereWasData = 1 \n  PRINT 'subProcedure had data'\nELSE\n  PRINT 'subProcedure had NO data'\n</code></pre>\n"}, {'answer_id': 12804872, 'author': 'Rikki', 'author_id': 1623728, 'author_profile': 'https://Stackoverflow.com/users/1623728', 'pm_score': 0, 'selected': False, 'text': "<p>I think you should do something like this:</p>\n\n<pre><code>Create  Procedure   [dbo].[GetResult]   (\n    @RowCount   BigInt  =   -1  Output\n)   As  Begin\n\n    /*\n        You can do whatever else you should do here.\n    */\n\n    Select  @RowCount   =   Count_Big(*)\n        From    dbo.SomeLargeOrSmallTable\n        Where   SomeColumn  =   'Somefilters'\n        ;\n\n    /*\n        You can do whatever else you should do here.\n    */\n\n    --Reporting how your procedure has done the statements. It's just a sample to show you how to work with the procedures. There are many ways for doing these things.\n    Return  @@Error;\n\nEnd;\n</code></pre>\n\n<p>After writing that you can get the output result like this:</p>\n\n<pre><code>Declare @RowCount   BigInt\n,   @Result     Int\n;\n\nExecute @Result =   [dbo].[GetResult]   @RowCount   Out\n\nSelect  @RowCount\n,   @Result\n;\n</code></pre>\n\n<p>Cheers</p>\n"}, {'answer_id': 39282002, 'author': 'M.Hassan', 'author_id': 3142139, 'author_profile': 'https://Stackoverflow.com/users/3142139', 'pm_score': 0, 'selected': False, 'text': '<pre><code>  create proc test\n    as\n    begin\n     select top 10 * from customers\n    end\n    go\n\n\n    create proc test2 (@n int out)\n    as\n    begin\n    exec test\n    set @n = @@rowcount\n    --print @n\n    end\n    go\n\n    declare @n1 int =0\n\n    exec test2 @n1 out\n    print @n1\n    --output result: 10\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/79856', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/10630/']