user107242 user107242 - 1 month ago 7
SQL Question

Stored Procedure with variable condition and error message

In my procedure, the variable @year should take two forms: 1: If @Year=1, Select all years. 2: @Year= the entered year.

Here's a sample of my code:

CREATE PROC spProcName (@Year)
AS
BEGIN
SELECT Year AS [YEAR], Item AS [ITEM]
FROM Sales
WHERE Year = @YEAR


I can make it work for @Year = 2013, but I don't know how to incorporate the @Year =1 to select all years. I'm guessing it would be with CASE.

I also have many other similar conditions with other variables, so I can't just create an IF statement.

Answer
Where Year = Case when @Year = 1 Then Year else @Year end
Comments