Quote:
but there is CreateDate in my table
In the table there is but the query hides the table. You use FROM (sub-query) from instead of FROM [table-name]. In this case the only columns in the sub-query accessible in the where clause and not all the columns in the table...
You may change it like this
FROM
(
SELECT P.DocStatus,C.CustomerName,P.CustomerID, CreateDate
FROM dbo.CRF_Project P
INNER JOIN dbo.CustomerList C
ON P.CustomerID = C.CustomerID
) p
UPDATE according OPs comments...
FROM
(
SELECT P.DocStatus,C.CustomerName,P.CustomerID
FROM dbo.CRF_Project P
INNER JOIN dbo.CustomerList C
ON P.CustomerID = C.CustomerID
WHERE Year([CreateDate])= (' +@YEAR+ ')'
) p
And
IF(@CUSTNAME != '' AND @YEAR = '')
SELECT @SQL = @SQL + ' WHERE CustomerName IN (' + @CUSTNAME + ')'
ELSE IF(@CUSTNAME = '' AND @YEAR != '')
SELECT @SQL = @SQL + ' WHERE Year([CreateDate])= (' +@YEAR+ ')'
ELSE
SELECT @SQL = @SQL