sql sp year or year and month

Answer 1

SELECT sum(col) 
FROM [yourtable]
WHERE year(dateTime) = @TargetYear 
    AND (@targetMonth < 0 OR month(dateTime) = @targetMonth)

Answer 2

I like Joel's answer except his won't work if you pass in a zero as the month. You will want @targetMonth=0 for example:

SELECT sum(col) 
WHERE year(dateTime) = @TargetYear AND 
(@targetMonth = 0 OR month(dateTime) = @targetMonth)

Answer 3

The stored proc can have an optional parameter:

Create PROCEDURE Report( @targetYear int, @targetMonth int = null )

It can be called either with the parameter or not:

exec Report 2009
exec Report 2009, 2

Then in your logic check for a null:

SELECT sum(col) 
FROM [yourtable]
WHERE (year(dateTime) = @TargetYear and @targetMonth = null)
  OR (year(dateTime) = @TargetYear AND @targetMonth = month(dateTime))

or

SELECT sum(col) 
FROM [yourtable]
WHERE year(dateTime) = @TargetYear 
  AND (@targetMonth = null OR year(dateTime) = @TargetYear)