DECLARE @SQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
SELECT @SQL = 'SELECT [0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
FROM
(
SELECT P.DocStatus, YEAR(createdate) as specYear
FROM dbo.CRF_Project P
INNER JOIN dbo.CustomerList C
ON P.CustomerID = C.CustomerID
) p
PIVOT
(
COUNT (DocStatus) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])
) AS pvt '
IF(@YEAR != ' ')
SELECT @SQL = @SQL + ' WHERE specYear = ' + @YEAR +''
ELSE
SELECT @SQL = 'SELECT [0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
FROM
(
SELECT P.DocStatus
FROM dbo.CRF_Project P
INNER JOIN dbo.CustomerList C
ON P.CustomerID = C.CustomerID
) p
PIVOT
(
COUNT (DocStatus) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])
) AS pvt '
SELECT @params = '@YEAR VARCHAR(MAX)'
SELECT @SQL = @SQL
EXEC sp_executesql @SQL, @params, @YEAR
END
split the 2 conditions into 2 sql statement, one with where clause and one without where clause. And also the 'SELECT' in subquery.