Monday, March 26, 2012

Incorrect syntax near the keyword 'IF'

After I added a second IF statement into the area of the JOIN operations, it started to complain. When I had only the first IF statement to determine whether to include the INNER JOIN for @.TermID, all was fine. So I am not sure why after adding that second IF check for the second JOIN operation it's complaining now:

SELECT @.sql =
'SELECT DISTINCT a.[FirstName] + '' '' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[ppStaffID],
s.[Email] AS AlternateEmail,
s.[Addr1] + '' '' + s.[Addr2] + '' '' + s.[City] + '','' + s.[State] + '' '' + s.[Zip] + '' '' + dbo.aaGetCountryDescription(s.[ppCountryId]) AS PrimaryAddress,
a.[GAddress1] + '' '' + a.[GAddress2] + '' '' + a.[GCity] + '' '' + a.[GState] + '' '' + a.[GZip] + '' '' + dbo.aaGetCountryDescription([GadCountryId]) AS ShippingAddress
FROM [aaStaff] a
INNER JOIN cmydb.dbo.[ppStaff] s ON s.[ppStaffId] = a.[ppStaffId]
INNER JOIN Mydb2.dbo.TtUsers u ON u.ppStaffId = s.ppStaffID
LEFT JOIN mydb2.dbo.ttUserRoles ur ON ur.ttUserId = u.ttUserId AND ur.ttRoleId = @.xttRoleId'

-- Check Whether to include Term Join
IF @.TermID IS NOT NULL
SELECT @.sql = @.sql + ' INNER JOIN cmydb.dbo.AdClassSchedTerm st ON st.AdTermID = @.xTermID ' + CHAR(13) +
-- Check Whether to include Course Taught Join
IF @.TaughtCourseID IS NOT NULL
SELECT @.sql = @.sql + ' INNER JOIN cmydb.dbo.adClassSched sc ON sc.AdCourseID = @.xTaughtCourseID AND convert(varchar,EndDate,101) < GetDate()' + CHAR(13) +
'WHERE 1 = 1
AND s.[Active] = 1
AND u.[Active] = 1
AND u.[ppCampusID] = @.xppCampusID' + CHAR(13)
IF @.FirstName IS NOT NULL
SELECT @.sql = @.sql + ' AND (a.[FirstName] LIKE @.xFirstName OR @.xFirstName = ''%%'' OR @.xFirstName IS NULL)' + CHAR(13)
IF @.LastName IS NOT NULL
SELECT @.sql = @.sql + ' AND (a.[LastName] LIKE @.xLastName OR @.xLastName = ''%%'' OR @.xLastName IS NULL)'

Incorrect syntax near the keyword 'IF'.

You have a dangling '+' operator; try removing the trailing '+' from this line:

IF @.TermID IS NOT NULL
SELECT @.sql = @.sql + ' INNER JOIN cmydb.dbo.AdClassSchedTerm st ON st.AdTermID = @.xTermID ' + CHAR(13) +

|||

errr...yea. I was thinking I had to leave those in to concatenate it if it was not null...but I only need that trailing + on my last if statement, not the first inner join.

Thanks!

No comments:

Post a Comment