sql sp year or year and month

Hi,

I have some SP that takes year and month:

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

select sum(col) where year(dateTime) = @targetYear and month(dateTime) = @targetMonth Then I have the same thing for year only

Create PROCEDURE Report( @targetYear int )

select sum(col) where year(dateTime) = @targetYear Of course, the logic is more complicated than sum(col)

My question is, how can I write this SP so the logic is not repeated across the two SP, even if it means passing 0 for target month when I mean the whole year?

From stackoverflow

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

• 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)
  

• 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)