Dynamic stored procedure returns error and can't find culprit

DeepToot

I have a stored procedure that I am doing dynamically and for some reason the error thrown is Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '.'. The stored procedure is this: ALTER PROCEDURE [dbo].[proc_AgencySearchDarrell] @Mortgagee varchar(50) = NULL, @ClosingDateBegin datetime = NULL, @ClosingDateEnd datetime = NULL, @MortgageId int = NULL, @County varchar(50) = NULL AS SET NOCOUNT ON DECLARE @SQL Nvarchar(4000) SELECT @SQL = 'SELECT DISTINCT M.MortgageId, T1.Agent, T1.Agency, T3.InsCompanyUW,BS1.FirstName' + ' ' + 'BS1.LastName, BS2.FirstName' + ' ' + 'BS2.LastName, L1.BrokerageBus, L2.Originator, L3.Mortgagee, RE1.Appraiser, RE2.BuyersBroker, RE3.BuyersSalesperson, P1.StreetAddress, P2.City, CONVERT(varchar,M.TransClosingDate,101) AS TransClosingDate, P3.County FROM Mortgage M LEFT OUTER JOIN TitleInfo T1 ON M.MortgageId = T1.MortgageId LEFT OUTER JOIN TitleInfo T2 ON M.MortgageId = T2.MortgageId LEFT OUTER JOIN TitleInfo T3 ON M.MortgageId = T3.MortgageId LEFT OUTER JOIN BuyerSellerInfo BSI1 ON M.MortgageId = BSI1.MortgageId LEFT OUTER JOIN BuyerSeller BS1 ON BSI1.BuyerId = BS1.BuyerSellerId LEFT OUTER JOIN BuyerSellerInfo BSI2 ON M.MortgageId = BSI2.MortgageId LEFT OUTER JOIN BuyerSeller BS2 ON BSI2.SellerId = BS2.BuyerSellerId LEFT OUTER JOIN LoanInfo L1 ON M.MortgageId = L1.MortgageId LEFT OUTER JOIN LoanInfo L2 ON M.MortgageId = L2.MortgageId LEFT OUTER JOIN LoanInfo L3 ON M.MortgageId = L3.MortgageId LEFT OUTER JOIN RealEstateInfo RE1 ON M.MortgageId = RE1.MortgageId LEFT OUTER JOIN RealEstateInfo RE2 ON M.MortgageId = RE2.MortgageId LEFT OUTER JOIN RealEstateInfo RE3 ON M.MortgageId = RE3.MortgageId LEFT OUTER JOIN PropertyInfo P1 ON M.MortgageId = P1.MortgageId LEFT OUTER JOIN PropertyInfo P2 ON M.MortgageId = P2.MortgageId LEFT OUTER JOIN PropertyInfo P3 ON M.MortgageId = P3.MortgageId WHERE 1=1 ' IF @MortgageId IS NOT NULL SELECT @SQL = @SQL + ' AND (M.MortgageId = COALESCE(@MortgageId,M.MortgageId)) ' IF @Mortgagee IS NOT NULL SELECT @SQL = @SQL + ' AND (L3.Mortgagee = COALESCE(@Mortgagee,L3.Mortgagee)) ' IF @County IS NOT NULL SELECT @SQL = @SQL + ' AND (P3.County = COALESCE(@County,P3.County))' IF @ClosingDateBegin IS NOT NULL SELECT @SQL = @SQL + ' AND (M.TransClosing

R Giskard Reventlov

Without attempting to run it myself if I get these errors with dynamic sql I always do a quick print @sql and see what I've got. Copy it into a new query window and run it. That should give you more of an idea.

me, me, me "The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!" Larry Niven

DeepToot

Great idea, I'll try that. Thanks

DeepToot

This worked and it was the concatenation of the FirstName and LastName's Thank you again for the time.

J4amieC

Swelborn wrote:

This worked and it was the concatenation of the FirstName and LastName's

Yup, that was on Line 4 [of the dynamic part] - exactly as the error message prompted.