CODEWITHSUNDEEP
sql-serverstored-procedures
Koosh
Koosh

Reputation: 896

only display @@RowCount in Stored Procedure

Is there any way to display only the @@RowCount when I run a stored procedure

My SP looks like this:

ALTER PROCEDURE [dbo].[sp_ClientCount] (
@DateFrom AS DATETIME, 
@DateTo AS DATETIME,
@ClientCount as INT = NULL 
) AS
select tblClients.ClientID, count(*)
   FROM   tblReqPayment INNER JOIN
          tblClients ON tblReqPayment.ClientID = 
tblClients.ClientID
   WHERE  BETWEEN @DateFrom and @DateTo
          GROUP By tblClients.ClientID
   HAVING count(*) = @ClientCount

   SELECT @@ROWCOUNT as 'Count'

However, I am seeing both results from my initial SELECT statement, as well as from my SELECT @@RowCount statement. Is there any way to ONLY display @@RowCount?

Upvotes: 2

Views: 501

Answers (2)

JamieD77
JamieD77

Reputation: 13949

you might want to consider giving your @ClientCount a default value other than NULL or rewrite your query to see if it @ClientCount is null before running the rest of it, since it will always be 0.

alter procedure [dbo].[sp_ClientCount] (
    @DateFrom as datetime, 
    @DateTo as datetime,
    @ClientCount as int = null 
)
as 
begin

    if @ClientCount IS NULL
        return 0

    select      count(ClientID)
    from        tblClients
    where       ClientID IN (
                        select      ClientID
                        from        tblReqPayment
                        where       somedate between @DateFrom and @DateTo
                        group by    ClientID
                        having      count(*) = @ClientCount
                ) 
end

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

You could simplify this a little bit. Something like this.

ALTER PROCEDURE [dbo].[sp_ClientCount] 
(
    @DateFrom AS DATETIME, 
    @DateTo AS DATETIME,
    @ClientCount as INT = NULL 
) AS
    set nocount on;

    select count(*) as ClientCount
    from
    (
        select count(*)
        FROM tblReqPayment p
        INNER JOIN tblClients c ON p.ClientID = c.ClientID
        WHERE ??? BETWEEN @DateFrom and @DateTo
        GROUP By c.ClientID
        HAVING count(*) = @ClientCount
    ) x

Your where clause posted has only have the predicate so idea what that should be. Additionally you should consider not using the prefix sp_ (or even better no prefix at all). That prefix can cause you some grief. https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Upvotes: 3

Related Questions