Stored Procedure Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure Error

Author

Topic

mikeb55
Starting Member

11 Posts

Posted - 2009-10-02 : 13:24:03

Hello,

I'm getting the following error when I run this code: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'. Any help would be appreciated. Thank you.

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., 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], , [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], , [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], , [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], , [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], , [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

Sincerely,

MB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 13:29:01

what does PRINT (@SQL) give you?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-02 : 15:04:41

First we will see if we can read that painful statement...

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

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-02 : 15:51:14

DECLARE @ParameterDefinition AS NVARCHAR(100) is too short for
'@Month int, @Year int, @newYear int, @EndYearMonth int, @PreviousMonthInt int, @OldYear int, @PreviousMonthName varchar(10), @NextMonthName varchar(10)'

No, you're never too old to Yak'n'Roll if you're too young to die.

mikeb55
Starting Member

11 Posts

Posted - 2009-10-05 : 07:35:39

After a very long time of looking at the stored procedure, I realized that I declared the ParameterDefinition of only 100 characters. And yes, this was a very painful stored procedure to write. I wish there was an easier way to display the data the way I needed it, but PIVOT was the only way. You have to see the rest of it, its a few pages long.

Thank you for your help.

Sincerely,

MB

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-05 : 08:41:45

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources