Creating stored procedure that returns the row count only

Question

I want to query a table and get the total number of rows that fit the given query:

ALTER PROCEDURE [dbo].[GetTransactionsFromTransactionRecordForMonthYear]
    @Guid   nvarchar(50),
    @Month  int,
    @Year   int
AS
BEGIN
    SET NOCOUNT ON; 

    SELECT  *
    FROM    Company.TransactionRecord
    WHERE   Company.TransactionRecord.CorporationId = @Guid and 
    MONTH( Company.TransactionRecord.TransactionDate ) = @Month AND 
    YEAR( Company.TransactionRecord.TransactionDate ) = @Year
    SELECT @@ROWCOUNT
END

When I run this, SSMS returns back 5783 rows but the return value is 5781. I would have thought that the total number of records would match the return value.

I'm not sure what I am doing wrong. How do I fix this where it just returns the total number of records for that query?

Thank you!

Answer 1

I agree with Linker that a scalar valued function is a better approach. I just think that the proposed code is a total mess and should be cleaned up as well:

ALTER FUNCTION [dbo].GetTransactionsFromTransactionRecordForMonthYear
(
    @Guid  uniqueidentifer,
    @Month int,
    @Year  int
)
RETURNS INT
AS
BEGIN    
    DECLARE @row_count INT;
  
    SELECT @row_count = COUNT(*)
    FROM Company.TransactionRecord tr
    WHERE tr.CorporationId = @Guid AND
          tr.TransactionDate >= DATEFROMPARTS(@Year, @Month, 1) AND
          tr.TransactionDate < DATEADD(MONTH, 1, DATEFROMPARTS(@Year, @Month, 1));

    RETURN @row_count;
END;

Notes:

• SQL has a type for unique identifiers. Presumably, that is what your GUID is. If it is a string, then use a string.
• The date arithmetic is sargable (i.e. better for the optimizer) if it uses direct date comparisons.
• Repeating the entire table name for each column just makes the code hard to write and read. Use table aliases!