Dynamic Parameters In Pivot

I know the premise of a PIVOT() this syntax produces it perfectly for me:

Select *
FROM 
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot 
(
  SUM(Amt)
  ,for annual IN ([2014],[2015],[2016])
) p

However, what if I am using a stored procedure and passing a year1 and year2 to the pivot as variables? This will not work as incorrect syntax, but could this be achieved?

Create Procedure dbo.PivotParams
As
Declare @year1 varchar(100), @year2 varchar(100)

Set @year1 = '2014'
Set @year2 = '2016'

Select *
FROM 
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot 
(
  SUM(Amt) for annual BETWEEN @year1 and @year2
) p

Also the output should use COALESCE to prevent any NULL values in any of the pivot columns.

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose):

DECLARE @year1 smallint, @year2 smallint

SET @year1 = 2014;
SET @year2 = 2016;

DECLARE @years nvarchar(max) = N'', @select nvarchar(max) = N'';

SELECT  @years += N',' + QUOTENAME(y), @select += N',
  ' + QUOTENAME(y) + N' = COALESCE(' + QUOTENAME(y) + N',0)'
FROM 
(
  SELECT TOP (@year2-@year1+1) 
    y = @year1 - 1 + ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects ORDER BY [object_id]
) AS y;

DECLARE @sql nvarchar(max) = N'Select field1' + @select + N'
FROM 
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM dbo.Testing -- ALWAYS use schema prefix!
) x
pivot 
(
  SUM(Amt) for annual IN (' + STUFF(@years, 1, 1, N'') + N')
) p;';

PRINT @sql;

-- EXEC sys.sp_executesql @sql;

Slightly related:

• Script to create dynamic PIVOT queries in SQL Server

StackExchange Database Administrators Q#139391, answer score: 4