Incorrect syntax near the keyword ''IF''

Question

Post reply

Incorrect syntax near the keyword ''IF''

dba321

Hall of Fame

Points: 3917
More actions
April 18, 2007 at 10:27 am

#119563

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 (IF @TaughtCourseID IS NOT NULL
) 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'.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Try this

if @TermID IS NOT NULL

begin

...

end

IF @TaughtCourseID IS NOT NULL

begin

...

end

dba321 Hall of Fame Points: 3917 More actions · Answer 2

you know what it was? I had to take out the first + after the Char(13) but keep it after the last IF Inner join

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ya, those big concatenated queries are often full of surprises like this one .