CODEWITHSUNDEEP
sql-serverservicelayersap-business-one-di-api
Deepesh G
Deepesh G

Reputation: 33

Can we create a stored procedure or a SQL function in a B1 Database using Service layer?

In DI API there was recordset object through which we can execute SQL queries directly to the database.
I want to create a SQL function and a SQL stored procedure inside the B1 database.

I tried to create a user query in Service layer User queries using the payload

{"SqlCode":"sql05","SqlName":"UserQuerySQLSP","SqlText":"create function [dbo].[TO_POParse] \r\n(@POString varchar(50), @FieldNeeded varchar(10))\r\nReturns int\r\nas\r\n\r\nbegin\r\nDECLARE @Number int\r\nif @FieldNeeded = 'DocNum'\r\nbegin \r\nset @Number =  LEFT(@POString, CHARINDEX ('-', @POString)-1)\r\nend\r\nif @FieldNeeded = 'LineNum'\r\nbegin \r\nset @Number =  replace(substring(@POString, CHARINDEX ('(', @POString)+1,3),')','') \r\nend\r\nif @FieldNeeded = 'Segment'\r\nset @Number =  substring(@POString, CHARINDEX ('-', @POString)+1,1)  \r\nreturn @Number \r\nend \r\nGO\r\n"}

and executed against the servicelayer url if our server.

https://<myservername>:50000/b1s/v1/SQLQueries

But getting error given below

{
    "error" : 
    {
        "code" : 701,
        "message" : 
        {
            "lang" : "en-us",
            "value" : "Invalid SQL syntax:, line 1, character position 0, mismatched input 'create' expecting {SELECT, '('}"
        }
    }
}

Is there any way or work around to accomplish to execute the CREATE , ALTER like SQL commands through servicelayer calls?

Upvotes: 0

Views: 195

Answers (0)

Related Questions