Query execution failed for Dataset1 Incorrect Syntax Near ','

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>

Answer 1

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

Answer 2

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

Answer 3

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