MSSQL Query executing correctly in azuredatastudio but failing to execute from code - Node JS

63 Views Asked by At

I'm executing a function in my NodeJs project that creates a trigger in a table in my database. I've tested the query in azuredatastudio and it works fine, but when i do it from code, it throws what is apparently a syntax error. I vaguely remember having a similar problem a while ago and the solution had to do with an issue with the BEGIN and END keywords and the Tedious driver used by the mssql npm library, but i haven't been able to find the documentation or thread from which i got the solution.

I'm using mssql ^7.2.0

Here's the query

CREATE TRIGGER [dbo].[SET_PRODUCT_UPDATE_SAEXIS] ON [dbo].[SAEXIS] AFTER Insert,UPDATE AS

DECLARE @sku VARCHAR(15), @CodUbic VARCHAR(10),  @name VARCHAR(40), @description VARCHAR(40), @categories INT, @regular_price VARCHAR(30), @sale_price VARCHAR(30), @manage_stock INT, @stock_quantity DECIMAL(28,4)

SELECT @sku = Codprod,@Codubic=Codubic FROM inserted
IF @Codubic='01' -- here is the supposed syntax error
BEGIN
    IF EXISTS (Select SKU from TBL_WC_WEB_PRODUCTS_OPERATIONS where operation='P')
    BEGIN
        SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity
        FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku
        DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku
        INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation)
        VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'C')
    END
    ELSE
    BEGIN
        SELECT @name = name, @description = description, @categories = categories, @regular_price = regular_price, @sale_price = sale_price, @manage_stock = manage_stock, @stock_quantity = stock_quantity
        FROM VW_WC_WEB_PRODUCTS WHERE sku = @sku
        DELETE FROM TBL_WC_WEB_PRODUCTS_OPERATIONS WHERE sku = @sku
        INSERT INTO TBL_WC_WEB_PRODUCTS_OPERATIONS (name, description, sku, categories, regular_price, sale_price, manage_stock, stock_quantity, operation)
        VALUES (@name, @description, @sku, @categories, @regular_price, @sale_price, @manage_stock, @stock_quantity, 'U')
    END
END

And the execution is done via the mssql's query method (request.query(queryStr))

Here's the error log

RequestError: Incorrect syntax near '01'.
    at handleError (/home/koe/Escritorio/code/work/wc_saint/node_modules/mssql/lib/tedious/request.js:384:15)
    at Connection.emit (node:events:527:28)
    at Connection.emit (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1065:18)
    at Parser.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1170:12)
    at Parser.emit (node:events:527:28)
    at Readable.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
    at Readable.emit (node:events:527:28)
    at addChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:298:12)
    at readableAddChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:280:11)
    at Readable.push (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:241:10) {
  code: 'EREQUEST',
  originalError: Error: Incorrect syntax near '01'.
      at handleError (/home/koe/Escritorio/code/work/wc_saint/node_modules/mssql/lib/tedious/request.js:382:19)
      at Connection.emit (node:events:527:28)
      at Connection.emit (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1065:18)
      at Parser.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/connection.js:1170:12)
      at Parser.emit (node:events:527:28)
      at Readable.<anonymous> (/home/koe/Escritorio/code/work/wc_saint/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
      at Readable.emit (node:events:527:28)
      at addChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:298:12)
      at readableAddChunk (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:280:11)
      at Readable.push (/home/koe/Escritorio/code/work/wc_saint/node_modules/readable-stream/lib/_stream_readable.js:241:10) {
    info: ErrorMessageToken {
      name: 'ERROR',
      event: 'errorMessage',
      number: 102,
      state: 1,
      class: 15,
      message: "Incorrect syntax near '01'.",
      serverName: 'garuda',
      procName: 'SET_PRODUCT_CREATE',
      lineNumber: 13
    }
  },
  number: 102,
  lineNumber: 13,
  state: 1,
  class: 15,
  serverName: 'garuda',
  procName: 'SET_PRODUCT_CREATE',
  precedingErrors: []
}
0

There are 0 best solutions below