sql sp年或年和月

I have some SP that takes year and month:

我有一些需要年份和月份的SP:

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

And querying it with:

并查询:

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
)

and querying it like:

并查询它:

select sum(col) where year(dateTime) = @targetYear

Of course, the logic is more complicated than sum(col)

当然,逻辑比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?

我的问题是,我怎么能写这个SP所以逻辑不会在两个SP上重复,即使这意味着当我意味着整年时,目标月传递0?

3 个解决方案

#1

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:

我喜欢乔尔的回答,但如果你在月份中输入零,那么他将无法工作。你会想要@ targetMonth = 0例如:

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

#2

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

#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:

然后在逻辑中检查null:

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

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

#1