存储过程帮助:将年份作为参数传递以获取结果

Question

我基本上是想输入一年（而不是整个日期）来获取一组结果，这些结果会告诉我今年的销售额。然而，我不知道该怎么做。下面是我写的一个例子。

ALTER PROC OrderYear
(@OrderYear datetime)
AS
SELECT Orderdate as [Year],COUNT(SalesOrderID) as Count
FROM Sales.SalesOrderHeader
WHERE @OrderYear=YEAR(OrderDate)
GROUP BY OrderDate
BEGIN
SET NOCOUNT ON
END
EXEC OrderYear '2005'

Answer 1

您希望将查询谓词表示为范围，因为范围是可搜索的：

SELECT Orderdate as [Year],COUNT(SalesOrderID) as Count
FROM Sales.SalesOrderHeader
WHERE OrderDate >= @firstDayOfYear AND OrderDate <= @lastDayOfYear
GROUP BY OrderDate;

因此，诀窍是计算给定年份的@firstDayOfYear和@lastDayOfYear。我将把这个留给读者练习。

Answer 2

程序正常，更改输入参数的类型：

ALTER PROC OrderYear
(@OrderYear int) 
AS
SELECT Orderdate as [Year],COUNT(SalesOrderID) as Count
FROM Sales.SalesOrderHeader
WHERE @OrderYear=YEAR(OrderDate)
GROUP BY OrderDate