locked
Query execution failed for Dataset1 Incorrect Syntax Near ',' RRS feed

  • Question

  • User-1499457942 posted

    Hi

     I have below code , above error comes when i select multiple Tenders. If 1 is selected then works fine.

    SELECT     distinct A.[Store No_], A.[Tender Code],

    <o:p></o:p>

                               (SELECT      STUFF<o:p></o:p>

                              ((SELECT     ',' + D.[Batch Number] AS [text()]<o:p></o:p>

                                  FROM         [Kamla Retail Limited$Store Batch Entry Line] D<o:p></o:p>

                                  WHERE     D.[Date] = A.[Date] AND D.[Store No_] = A.[Store No_] <o:p></o:p>

                                  and D.[Tender Code]=A.[Tender Code]<o:p></o:p>

                                  FOR XML PATH('')), 1, 1, ''))<o:p></o:p>

    AS [BO]<o:p></o:p>

    FROM         dbo.[ABS$Store Batch Entry Line] AS A <o:p></o:p>

    WHERE     (A.Date <= @date) AND (A.[Tender Code] In (@tender)) and A.[Store No_] in (@store)<o:p></o:p>

    Friday, December 1, 2017 4:51 AM

Answers

  • User991499041 posted

    Hi JagjitSingh,

    I tried to reproduce the problem, but I didn't see the problem.

    declare @a table([Batch Number] varchar(10),[Date] varchar(10),[Store No_] varchar(10),[Tender Code] varchar(10))
    declare @b table([Store No_] varchar(10),Date varchar(10),[Tender Code] varchar(10))
    
    insert into @a values('1','1','1','1'),('1','1','1','1'),('1','1','1','1')
    
    insert into @b values('1','1','1')
    
    SELECT     distinct A.[Store No_], A.[Tender Code],
    
                               (SELECT      STUFF
    
                              ((SELECT     ',' + D.[Batch Number] AS [text()]
    
                                  FROM         @a D
    
                                  WHERE     D.[Date] = A.[Date] AND D.[Store No_] = A.[Store No_]
    
                                  and D.[Tender Code]=A.[Tender Code]
    
                                  FOR XML PATH('')), 1, 1, ''))
    
    AS [BO]
    
    FROM         @b AS A
    --WHERE     (A.Date <= @date) AND (A.[Tender Code] In (@tender)) and A.[Store No_] in (@store)
    
    

    Basically there are couple of ways to debug stored procedure

    • Using SQL Management studio (SSMS)
    • Using Visual Studio

    In the process of debug, you will easily get the final execution of the SQL statement, believe that you will find the problem.

    Debug your stored procedure

    https://www.codeproject.com/Articles/1102547/Debug-your-stored-procedure

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 1, 2017 6:22 AM

All replies

  • User991499041 posted

    Hi jagjitSingh,

    The query seems correct, I guess the error was caused by the arguments.

    I notice that your script 

    WHERE     (A.Date <= @date) AND (A.[Tender Code] In (@tender)) and A.[Store No_] in (@store)

    If the parameter @tender is like this

    in (,'xxx')

    this can cause error Incorrect Syntax Near ','

    I would suggest you double check the parameter to see if it's a mistake like I said.

    Regards,

    zxj

    Friday, December 1, 2017 5:33 AM
  • User-1499457942 posted

    Hi

      How i can check this . I have given Default/Available Values in parameter. If only 1 Tender is selected then it works fine.

    Secondly if i remove below part then it works fine

    (SELECT      STUFF

                              ((SELECT     ',' + D.[Batch Number] AS [text()]

                                  FROM         [Kamla Retail Limited$Store Batch Entry Line] D

                                  WHERE     D.[Date] = A.[Date] AND D.[Store No_] = A.[Store No_]

                                  and D.[Tender Code]=A.[Tender Code]

                                  FOR XML PATH('')), 1, 1, ''))

    Thanks

    Friday, December 1, 2017 5:47 AM
  • User991499041 posted

    Hi JagjitSingh,

    I tried to reproduce the problem, but I didn't see the problem.

    declare @a table([Batch Number] varchar(10),[Date] varchar(10),[Store No_] varchar(10),[Tender Code] varchar(10))
    declare @b table([Store No_] varchar(10),Date varchar(10),[Tender Code] varchar(10))
    
    insert into @a values('1','1','1','1'),('1','1','1','1'),('1','1','1','1')
    
    insert into @b values('1','1','1')
    
    SELECT     distinct A.[Store No_], A.[Tender Code],
    
                               (SELECT      STUFF
    
                              ((SELECT     ',' + D.[Batch Number] AS [text()]
    
                                  FROM         @a D
    
                                  WHERE     D.[Date] = A.[Date] AND D.[Store No_] = A.[Store No_]
    
                                  and D.[Tender Code]=A.[Tender Code]
    
                                  FOR XML PATH('')), 1, 1, ''))
    
    AS [BO]
    
    FROM         @b AS A
    --WHERE     (A.Date <= @date) AND (A.[Tender Code] In (@tender)) and A.[Store No_] in (@store)
    
    

    Basically there are couple of ways to debug stored procedure

    • Using SQL Management studio (SSMS)
    • Using Visual Studio

    In the process of debug, you will easily get the final execution of the SQL statement, believe that you will find the problem.

    Debug your stored procedure

    https://www.codeproject.com/Articles/1102547/Debug-your-stored-procedure

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 1, 2017 6:22 AM