You can create a procedure that takes month and year, and optionally just return all data when no parameters are specified, then you can do this (I'm adding a bunch of date handling here instead of string conversion of functions like month/year so that, if crdate is indexed, you'll still have a query that can take advantage of the index):
USE tempdb;
GO
CREATE TABLE dbo.messages
(
x INT IDENTITY(1,1),
crdate DATETIME
);
INSERT dbo.messages(crdate) VALUES
('20100101'),('20100101'),('20100501'),('20110405'),('20120506')
GO
ALTER PROCEDURE dbo.GetYearCounts
@y INT = NULL,
@m INT = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sm DATETIME = DATEADD(MONTH, @m-1, DATEADD(YEAR, @y-1900, 0));
DECLARE @em DATETIME = DATEADD(MONTH, 1, @sm);
;WITH x(m,c) AS
(
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, crdate), 0),
COUNT(*)
FROM dbo.messages
WHERE crdate >= COALESCE(@sm, '19000101')
AND crdate < COALESCE(@em, '20300101')
GROUP BY DATEDIFF(MONTH, 0, crdate)
)
SELECT y = YEAR(m), m = MONTH(m), tally = c
FROM x
ORDER BY y,m;
END
GO
Now results of two different calls:
EXEC dbo.GetYearCounts 2010, 1;
y m tally
---- --- -----
2010 1 2
EXEC dbo.GetYearCounts;
y m tally
---- --- -----
2010 1 2
2010 5 1
2011 4 1
2012 5 1
If you want to optionally specify a year only and get all 12 months, or specify a month only and get that month across multiple years, the procedure can be modified slightly. It's also tough to tell from your requirements if you want a row even for any months were there were no activity - that can be accomplished as well but we need better requirements.
where
clause (not thegroup by
clause) won't "clump all the months together". Why do you want that year filter in the where clause?