SQL Server Performance

How to EXEC a SP to get a row count, but not return the table, only the number of rows?

Discussion in 'T-SQL Performance Tuning for Developers' started by abradley81, Jan 4, 2008.

  1. abradley81 New Member

    I have a standard search stored procedure I use to search all the products. I pass it the search term parameters and it returns a table of all the products like it should:
    EXEC inqSearch 'red shirt'
    What I would also like to do, in another stored procedure, is see how many results I would get and return that number of rows, but not return the actual table. I'd like to use the exact same inqSearch stored procedure so i'm not duplicating code.
    Currently I run the search, then return a table of the previous row count:EXEC
    inqSearch 'red shirt';
    SELECT @@ROWCOUNT as totalFound
    This way works in that the second table returns the total number of products, but I don't need to return a whole table of red shirt products like what the first table does.
    Is there a way I can get the count like above, then cancel, release, dispose, free, etc, the previous table so I don't have retrieve all that data? Or is there a way I can execute the SP so I can just get the row count and nothing more?
  2. Adriaan New Member

    Let your stored procedure do a rowcount instead of selecting all the rows for the given criteria ...
    CREATE PROCEDURE dbo.inqSearch (@criteria VARCHAR(100), @rowcount INT OUTPUT)
    SELECT @rowcount = COUNT(*)
    FROM MyTable
    WHERE MyColumn = @criteria

    You can call that like this:

    DECLARE @rows INT
    EXEC dbo.inqSearch 'red shirt', @rows OUTPUT
    ... and after this, the @rows variable contains the number of rows.

    If you just want to 'print' the results after the call, drop the OUTPUT parameters, and make this the body of the proc:
    FROM MyTable
    WHERE MyColumn = @criteria
  3. Madhivanan Moderator

Share This Page