DECLARE @SQL1 NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
SELECT @SQL1 ='SELECT P.DocStatus,C.CustomerName,P.CustomerID, YEAR(P.CreateDate) as createYear
FROM dbo.CRF_Project P
INNER JOIN dbo.CustomerList C
ON P.CustomerID = C.CustomerID'
IF(@CUSTNAME != '')
SELECT @SQL1 = @SQL1 + ' WHERE C.CustomerName IN (' + @CUSTNAME + ')'
ELSE
SELECT @SQL1= @SQL1
IF(@YEAR != '')
SELECT @SQL1 = @SQL1 + ' WHERE YEAR(P.CreateDate) = ' +@YEAR+ ''
ELSE
SELECT @SQL1 = @SQL1
SELECT @SQL2 = 'SELECT CustomerName,[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(' + @SQL1 +
' ) p
PIVOT
(
COUNT (createYear) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])
) AS pvt '
SELECT @params = '@CUSTNAME VARCHAR(MAX),
@YEAR VARCHAR(MAX)'
EXEC sp_executesql @SQL2, @params, @CUSTNAME, @YEAR
END