[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ssrs 2008 "incorrect syntax near ')'"

Posted on 2011-05-02
4
Medium Priority
?
1,203 Views
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!
rptPayTypeLimits.xml

The T-SQL:
/****** Object:  StoredProcedure [dbo].[RptPayTypeLimitsReport]    Script Date: 04/19/2011 15:08:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
      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.

      ORIGINAL SPECIFICATIONS:
      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:
      Date
      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)
)
AS
      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) =
            'SELECT
                  emp.OrgID,
                  emp.EmployeeNumber,
                  emp.LastName + '', '' + emp.FirstName as [EmployeeName],
                  td.HoursDate,
                  td.Code07 as [ProjectAbbreviation],
                  td.Paytype,
                  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,
                        emp.EmployeeNumber,
                        emp.LastName,
                        emp.FirstName,
                        td.HoursDate,
                        td.Code07,
                        td.Paytype
            '
      IF (@OverUnder = 'OVER')
            SET @TSQL = @TSQL + 'HAVING sum(td.Amount) >= ' + LTRIM(RTRIM(STR(@Hours))) + ';';
      ELSE
            SET @TSQL = @TSQL + 'HAVING sum(td.Amount) <=  ' + LTRIM(RTRIM(STR(@Hours))) + ';';

      PRINT @TSQL;
      EXECUTE (@TSQL);
      
SET NOCOUNT ON
GO

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

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

Expert Comment

by:Rimvis
ID: 35510286
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
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35510534
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:
ALLREGULAR,ALLOVERTIME,ALLEXPENSE
The udf_GetPayTypesByCategory function should also be able to deal with this.
0
 
LVL 21

Expert Comment

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

Author Closing Comment

by:Miles Thornton
ID: 35512737
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!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 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