I am getting an error saying incorrect syntax near f
It works in SQL Server 2005, but I cannot get it to work in SQL Server 2000
The error appears to be in the section that I marked in Bold.
CREATE PROCEDURE [dbo].[pe_getReport]
-- Add the parameters for the stored procedure here
@.BranchID INT,
@.InvestorID INT,
@.Status INT,
@.QCAssigned INT,
@.LoanOfficer nvarChar(40),
@.FromCloseDate DateTime,
@.ToCloseDate DateTime,
@.OrderBy nvarChar(50)
AS
DECLARE
@.l_Sql NVarChar(4000),
@.l_OrderBy NVarChar(500),
@.l_OrderCol NVarChar(150),
@.l_CountSql NVarChar(4000),
@.l_Where NVarChar(4000),
@.l_SortDir nvarChar(4)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @.l_Where = N' Where 1=1'
IF (@.BranchID IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.BranchID=' + CAST(@.BranchID As NVarChar)
IF (@.Status IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.Status=' + CAST(@.Status As NVarChar)
IF (@.InvestorID IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.InvestorID=' + CAST(@.InvestorID As NVarChar)
IF (@.QCAssigned IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.QCAssigned=' + CAST(@.QCAssigned As NVarChar)
IF (@.LoanOfficer IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.LoanOfficer LIKE ''' + @.LoanOfficer + '%'''
IF (@.FromCloseDate IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.ClosingDate>=''' + CAST(@.FromCloseDate AS NVarChar) + ''''
IF (@.ToCloseDate IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.ClosingDate<=''' + CAST(@.ToCloseDate AS NVarChar) + ''''
IF @.OrderBy IS NULL
SET @.OrderBy = 'DateEntered DESC'
SET @.l_SortDir = SUBSTRING(@.OrderBy, CHARINDEX(' ', @.OrderBy) + 1, LEN(@.OrderBy))
SET @.l_OrderCol = SUBSTRING(@.OrderBy, 1, NULLIF(CHARINDEX(' ', @.OrderBy) - 1, -1))
IF @.l_OrderCol = 'InvestorName'
SET @.l_OrderBy = 'i.InvestorName ' + @.l_SortDir
ELSE IF @.l_OrderCol = 'BName'
SET @.l_OrderBy = 'b.BName ' + @.l_SortDir
ELSE IF @.l_OrderCol = 'StatusDesc'
SET @.l_OrderBy = 's.StatusDesc ' + @.l_SortDir
ELSE IF @.l_OrderCol = 'QCAssigned'
SET @.l_OrderBy = 'q.LoginName ' + @.l_SortDir
ELSE SET @.l_OrderBy = 'f.' + @.l_OrderCol + ' ' + @.l_SortDir
SET @.l_CountSql = 'SELECT f.FundedID As FundedID FROM FundedInfo AS f LEFT OUTER JOIN
Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN
Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN
Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN
QCLogins AS q f.QCAssigned = q.LoginID '
+ @.l_Where + ' ORDER BY ' + @.l_OrderBy
CREATE TABLE #RsltTable (ID int IDENTITY PRIMARY KEY, FundedID int)
INSERT INTO #RsltTable(FundedID)
EXECUTE (@.l_CountSql)
SELECT f.DateEntered As DateEntered, f.LastName As LastName, f.LoanNumber As LoanNumber,
f.LoanOfficer As LoanOfficer, f.ClosingDate As ClosingDate,
i.InvestorName As InvestorName, b.BName As BName, s.StatusDesc As StatusDesc,
q.LoginName As LoginName
FROM
FundedInfo AS f LEFT OUTER JOIN
Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN
Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN
Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN
QCLogins As q ON f.QCAssigned = q.LoginID
WHERE FundedID IN(SELECT FundedID FROM #rsltTable)
ORDER BY
CASE @.OrderBy WHEN 'DateEntered ASC' THEN f.DateEntered END ASC,
CASE @.OrderBy WHEN 'DateEntered DESC' THEN f.DateEntered END DESC,
CASE @.OrderBy WHEN 'LastName ASC' THEN f.LastName END ASC,
CASE @.OrderBy WHEN 'LastName DESC' THEN f.LastName END DESC,
CASE @.OrderBy WHEN 'LoanNumber ASC' THEN f.LoanNumber END ASC,
CASE @.OrderBy WHEN 'LoanNumber DESC' THEN f.LoanNumber END DESC,
CASE @.OrderBy WHEN 'LoanOfficer ASC' THEN f.LoanOfficer END ASC,
CASE @.OrderBy WHEN 'LoanOfficer DESC' THEN f.LoanOfficer END DESC,
CASE @.OrderBy WHEN 'ClosingDate ASC' THEN f.ClosingDate END ASC,
CASE @.OrderBy WHEN 'ClosingDate DESC' THEN f.ClosingDate END DESC,
CASE @.OrderBy WHEN 'InvestorName ASC' THEN i.InvestorName END ASC,
CASE @.OrderBy WHEN 'InvestorName DESC' THEN i.InvestorName END DESC,
CASE @.OrderBy WHEN 'BName ASC' THEN b.BName END ASC,
CASE @.OrderBy WHEN 'BName DESC' THEN b.BName END DESC,
CASE @.OrderBy WHEN 'StatusDesc ASC' THEN s.StatusDesc END ASC,
CASE @.OrderBy WHEN 'StatusDesc DESC' THEN s.StatusDesc END DESC,
CASE @.OrderBy WHEN 'LoginName ASC' THEN q.LoginName END ASC,
CASE @.OrderBy WHEN 'LoginName DESC' THEN q.LoginName END DESC
END
GO
