Stored Procedure Error

Question

Hello,

I'm getting the following error when I run this code: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. Any ideas on how to fix this? Any help would be appreciated.

Declare @Month int 
Declare @Year int
Declare @newYear int 
Declare @EndYearMonth int 
Declare @PreviousMonthInt int 
Declare @OldYear int 
Declare @PreviousMonthName varchar(10)
Declare @NextMonthName varchar(10)
Declare @SQL nvarchar(max)
DECLARE @ParameterDefinition AS NVARCHAR(100)
SET @ParameterDefinition =  '@Month int, @Year int, @newYear int, @EndYearMonth int,  @PreviousMonthInt int, @OldYear int, @PreviousMonthName varchar(10), @NextMonthName varchar(10)' 


Set @SQL = 'Select Y.*, X.* from (Select T1.[1], T1.[2], T1.[3], T1.[4], T1.[5], T1.[6], T1.[7], T1.[8], T1.[9], T1.[10],    T1.[11], T1.[12], T1.[13], T1.[14], T1.[15], T1.[16], T1.[17], T1.[18], T1.[19], T1.[20],    T1.[21], T1.[22], T1.[23], T1.[24], T1.[25], T1.[26], T1.[27], T1.[28], T1.[29], T1.[30], T1.[31], T2.[1] as February1, T2.DoctorName from   (SELECT [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], DoctorName  FROM   (SELECT DoctorName, WeekDay, DayType  FROM View_GridbyWeekDay where TheMonth = @Month and TheYear = @Year) ps  PIVOT  (  Max(DayType)  FOR WeekDay IN  ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])  ) AS pvt) as T1  Left outer Join    (SELECT [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], DoctorName  FROM   (SELECT DoctorName, WeekDay, DayType  FROM View_GridbyWeekDay where TheMonth = @EndYearMonth and TheYear = @newYear) ps   PIVOT  (  Max(DayType)  FOR WeekDay IN  ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])  ) AS pvt) as T2   on (T1.DoctorName = T2.DoctorName)) as X  Left Outer Join   (Select DoctorName, [27] as December27, [28] as December28, [29] as December29,   [30] as December30, [31] as December31  FROM   (SELECT DoctorName, WeekDay, DayType  FROM View_GridbyWeekDay where TheMonth = @PreviousMonthInt  and TheYear = @OldYear) ps  PIVOT  (  Max(DayType)  FOR WeekDay IN  ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])  ) AS pvt) as Y on (X.DoctorName = Y.DoctorName)  Order by X.DoctorName asc '

EXECUTE sp_executesql @SQL, @ParameterDefinition, @Month, @Year, @newYear, @EndYearMonth, @PreviousMonthInt, @OldYear, @PreviousMonthName,  @NextMonthName

Answer 1

You're missing at least one ending parentheses for your PIVOT command.

Edit: Looks like you have one too many.

I took all of the text out of your statement and just left the parentheses. This is what it looks like:

Select Y.*, X.* from 
((()(()()))(()(()))())(()(()))()

Answer 2

As @George Stocker said, You're missing at least one ending parentheses for your PIVOT command.

try taking your @SQL string in an editor and breaking it up onto multiple lines and indenting it properly, and you'll probably notice where the missing ")" should be.