Dynamic Column Query

ALTER PROCEDURE [dbo].[Matrix]
(
@StartDate varchar(20),
@EndDate varchar(20)
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @T AS TABLE(y int NOT NULL PRIMARY KEY)

INSERT INTO @T SELECT DISTINCT YEAR(orderdate) FROM dbo.Orders

DECLARE @cols AS nvarchar(MAX), @y AS int
SET @y = (SELECT MIN(y) FROM @T)
SET @cols = N''
WHILE @y IS NOT NULL
BEGIN
SET @cols = @cols + N',['+CAST(@y AS nvarchar(10))+N']'
SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))

-- Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT
custname,
YEAR(orderdate) AS orderyear,
ISNULL(qty,0) AS qty
FROM dbo.Orders o
INNER JOIN
Customer c
ON
o.custid=c.CustId
where orderdate between '''+ @StartDate + ''' and ''' + @EndDate + ''''+
') AS D '+
'PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P'
PRINT @sql -- for debugging
EXEC sp_executesql @sql


END