ssrs 2008 "incorrect syntax near ')'"

Posted on 2011-05-02
Last Modified: 2012-05-11
Hi Experts!
I have a situation where the report doesn't work, but the SQL does. It works in the designer when I just run the query and manually put in the variable values. It does not work when run in the preview mode; I get: incorrect syntax near ')'.

I am attaching the .rdl as an XML file and the query definition; thank you for your help!

The T-SQL:
/****** Object:  StoredProcedure [dbo].[RptPayTypeLimitsReport]    Script Date: 04/19/2011 15:08:42 ******/
      CO 24915: We want to alter the Pay Type Overage Report to have extra functionality. The original specifications can be found below. Please make the following changes:
      Name Change: Pay Type Limits Report
      Added Filter: Drop-down Box with no label to the immediate left of the Hours/Amount Text Entry field. The box would have two Options (Over and Under.)
      Currently the report returns any values for a selected pay type where the hours or amount is greater than or equal to the entered filter amount.
      I would like to expand the report to include the option to do 'greater than or equal to' AND 'less than or equal to.'
      Expanded Option: Make the Pay Type field multiple-select in addition to having the following options listed at the top:
            All Regular Pay Types, All Overtime Pay Types, All Expense Pay Types, and All Premium Pay Types.
      The Over/Under would calculate all pay types together rather than one pay type at a time.
      The 'All' option would allow the user to select all Pay Types of a particular type (Regular, Overtime, Premium, or Expense) at one time rather than individually.

      New TLS report requested: Users would like to see employees who went over a certain amount of hours for a selected pay type for a selected date range.
      Title: Pay Type Overage (Daily) Report
      Available Filters
      Org: Drop-down
      Pay Type: Drop-down field with a pay type list from the selected Org
      Hours/Amount Limit:
      Start Date: Calendar Field
      End Date: Calendar Field
      Filter Validations: Start Date cannot be earlier than End Date
      Report fields:
      Employee ID
      Employee Last Name, First Name
      Project Abbreviation: PA used for charged pay type
      Pay Type
      Total: Hours or Amount charged to pay type for the given day
      The report should return any time the employee went over filter limit for the selected pay type. The report listings should be grouped by Employee ID (sorted in descending alphanumeric order.) Entries within the Employee ID order should be sorted by date.
      The report header should include the report name and the pay type and date filters used.
-- DROP PROC [dbo].[rptPayTypeLimits];
CREATE  PROC [dbo].[rptPayTypeLimits]
      @OrgID int,
      @StartDate datetime,
      @EndDate datetime,
      @Hours int,
      @OverUnder nvarchar(5),
      @PayType nvarchar(max)
      DECLARE @PayTypesList nvarchar(max) = (dbo.udf_GetPayTypesByCategory(@PayType));
      PRINT '@PayTypesList = ' + @PayTypesList;
      DECLARE @nvcOrgID nvarchar(10) = CONVERT(nvarchar, @OrgID );
      PRINT '@nvcOrgID = ' + @nvcOrgID;
      PRINT '@OverUnder = ' + @OverUnder;
      DECLARE @TSQL nvarchar(max) =
                  emp.LastName + '', '' + emp.FirstName as [EmployeeName],
                  td.Code07 as [ProjectAbbreviation],
                  sum(td.Amount) as [Total]
            FROM vwTimesheetDetail As td WITH (NOLOCK)
            INNER JOIN vwEmployeeInfo As emp WITH (NOLOCK)
                  ON (emp.EmployeeNumber = td.EmployeeNumber)
            WHERE td.StartDate < td.EndDate
            AND td.isDeleted = 0
            AND emp.OrgID = ' + @nvcOrgID + '
            AND td.Paytype IN ( ' + @PayTypesList + ' )
          AND td.HoursDate BETWEEN ''' + CONVERT(varchar, @StartDate) +
            ''' AND  ''' + CONVERT(varchar,  @EndDate ) + '''
            GROUP BY emp.OrgID,
      IF (@OverUnder = 'OVER')
            SET @TSQL = @TSQL + 'HAVING sum(td.Amount) >= ' + LTRIM(RTRIM(STR(@Hours))) + ';';
            SET @TSQL = @TSQL + 'HAVING sum(td.Amount) <=  ' + LTRIM(RTRIM(STR(@Hours))) + ';';

      PRINT @TSQL;
      EXECUTE (@TSQL);

GRANT EXECUTE ON [dbo].[rptPayTypeLimits] TO PUBLIC;

-- Verification:
EXECUTE dbo.rptPayTypeLimits
      @OrgID = 228,
      @StartDate = '2011-01-01',
      @EndDate = '2011-05-02',
      @Hours = 5,
      @OverUnder = 'OVER',
      @PayType = 'REGULAR'
Question by:Miles Thornton
    LVL 19

    Expert Comment

    Hi Miles_Thornton,

    Make sure you are using same parameters when you run report and when you run your procedure manually.
    Try to run SQL Profiler and look if passed parameters are the same
    LVL 22

    Accepted Solution

    In your '-- Verification:' you use @PayType = 'REGULAR'. But in the report the parameter value is defined as 'ALLREGULAR'. When you test with this value you might get the syntax error also. I think the udf_GetPayTypesByCategory function returns empty string in this case. This makes your query:
    AND td.Paytype IN ()

    this causes the syntax error.
    Also keep in mind that the PayType parameter is multi value in the report so it might also be:
    The udf_GetPayTypesByCategory function should also be able to deal with this.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Is there any filter? Please check Query with same conditions in both environment.
    LVL 1

    Author Closing Comment

    by:Miles Thornton
    While Rimvis was essentially correct, the issue was parameter-based; Nicbo's answer was exactly spot-on and crystal clear.

    Thank you very much experts! Nicbo, YOU ROCK!

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    sql Audit table 3 21
    Using Case  in sql queries 17 51
    Oracle PL/SQL syntax 4 30
    Alter table to increase max Len 6 16
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video discusses moving either the default database or any database to a new volume.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now