Incorrect syntax near LEFT

Hi guys;

I am trying to create a stored procedure in my database with dynamic filter, how ever i cant even let my query work.

When i run my stored procedure i get this errorIncorrect syntax near 'LEFT'

I dnt knw wats wrong with my code.

Please help me...
below is my stored procedure..

Thanks is advance.

ALTER PROCEDURE dbo.GetClassByCustomFilter
@.pcCustomFilterNVARCHAR(500)=''
AS
DECLARE @.sSqlString nvarchar(1024)
SET @.sSqlString = 'SELECT R.cRoomNo, P.cFirstName, P.cLastName, P.cMiddleName, U.cCode AS cSubjectCode, U.cName AS cSubjectName, '
SET @.sSqlString = @.sSqlString + 'U.cDescription AS cSubjectDescription, B.cCode AS cSection, H.cName AS cRecurenceName, C.iClassID, C.iInstructorID, C.iSubjectid, C.iEnrollmentID, '
SET @.sSqlString = @.sSqlString + 'C.iRecordTypeID, C.iBlockSectionID, C.cCode, C.cType, C.iRoomID, C.cRecurrence, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, '
SET @.sSqlString = @.sSqlString + 'C.tStartTime,8) AS datetime) AS tStartTime, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, C.tEndTime,8) AS datetime) '
SET @.sSqlString = @.sSqlString + 'AS tEndTime, C.fUnits, C.nAllowed, C.nMaxAllowed, C.mNotes, C.tCreated, C.tEdited, C.iEditedBy, C.iCreatedby, C.adGUID '
SET @.sSqlString = @.sSqlString + 'FROM dbo.PERSONALINFO P INNER JOIN dbo.INSTRUCTORS I ON P.iPersonalInfoId = I.iPersonalInfoID RIGHT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.CLASSES C INNER JOIN dbo.BLOCKSECTIONS B ON C.iBlockSectionID = B.iBlockSectionId INNER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.ROOMS R ON C.iRoomID = R.iRoomId WHERE (C.cType <> ''0'')'
IF LEN(@.pcCustomFilter) > 0
BEGIN
SET@.sSqlString = @.sSqlString + ' AND ' + @.pcCustomFilter
END
EXEC sp_executesql @.sSqlString

Well, thanks for reminding me why I hate complex dynamic SQL strings.

Thisa line is causing you problems:

SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the:ON C.cRecurrence = H.cRecurrenceON I.iInstructorID = C.iInstructorID (two ON clauses for a single JOIN clause).|||Thanks for the reply .

The Query is correct.

I get this error because the lenght of the variable@.sSqlString is only 1024 but my query string is longer than that.

Regards|||Well, be that as it may, having to ON statements for a single JOIN clause will give you a syntax error...|||Of course it will. But the SQL statement below has 6JOIN and 6ON.

enewe thanks 4 the time.

Regards.|||The following, cut and pasted from you original post, is what I'm referencing:

SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the 'ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID' of the second line. That will cause issues.

However, I'm glad you got it working...|||Of course it wont work bcoz its just a part of the statement, not the whole statement. :) jst Kid'N. But I am 101% sure that the SQL Statement below will work because I just copied it from Visual Studio .NET View Designer.

8)