store procedure not working anymore in B1

[Former Member]

HI all, I have the attached store procedure not working in B1 8.8 PL16 anymore.

It returns error message: 'incorrect syntax near '0'

any ideas?

thanks

Rui

store_procedure_error.txt.zip

[Former Member · Accepted solution]

Hi Rui,

It seems that the problem is not in the syntax itself, but in the data used to build the dynamic query.

In order to check it, you need to evaluate the final SQL statement you're running.

Your code has 2 parts:

1. building a query statement.

2. running the query.

Can you tell which part fails to run? You can trace that by replacing the last row. Instead of:

Exec( @SQL+@SQLTT)

write

Select( @SQL+@SQLTT)

If you get results that means that the problem is in the 2nd part. You can evaluate the SQL statement you got to see the problem.

Beni.

[Former Member · Accepted solution]

thanks Benni but don´t see any problem with the query.

I´ve attached a picture of it

thanks again,

Rui

[Former Member · Accepted solution]

Hi,

Is this the whole sql statement? if so - you can see a syntax mistake at the end of the query.

Beni.

[Former Member · Accepted solution]

i found the issue: i´ve changed the 2 declare functions to nvarchar(max). it works fine now. thanks, Benny

[Former Member]

when I want to execute it in SAP B1 it gives the error message: incorrect syntax near '0'.

this store procedure was working fine but suddenly is giving this error, no patch updates were done in B1.

Message was edited by: Rui Pereira

[former_member184146]

Hi Rui,

your SP is working fine on my DB .

--Manish