Express JS API query variable with parameters for EXEC stored procedure

Question

I am working with the API which will serve as my data access layer in my project architecture.

This is the original form of my API query setup:

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query('EXEC testStoredProcedure @user_id, @img_data, @img_size, @birthday, @country, @email_add, @mobile_no, @key');

It works well with that setup.

However, if there are more parameters than expected, I want to improve the API query's readability with the same outcomes.

So I use these approaches using variable and backticks:

with req.body.[param]

const query = `
    'EXEC testStoredProcedure
    @user_id = ${req.body.user_id},
    @img_data = ${req.body.img_data},
    @img_size = ${req.body.img_size},
    @birthday = ${req.body.birthday},
    @country = ${req.body.country},
    @email_add = ${req.body.email_add},
    @mobile_no = ${req.body.mobile_no},
    @key = ${req.body.key}'
`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

without req.body

const query = `
    'EXEC testStoredProcedure
    @user_id = ${user_id},
    @img_data = ${img_data},
    @img_size = ${img_size},
    @birthday = ${birthday},
    @country = ${country},
    @email_add = ${email_add},
    @mobile_no = ${mobile_no},
    @key = ${key}'
`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

Both fails to process the request through this API call from my Flutter project:

await _api.testMethod(
    '/api/request', // route
    null, // user_id
    '', // img_data
    0.00, // img_size
    DateTime.now().toIso8601String(), // birthday
    'Philippines', // country
    null, // email_add
    _mobileNo, // mobile_no
    'SIGN_IN'); // key

This is what the logs say for with req.body.[param] and without req.body.[param]:

Uncaught exception: Exception: Failed to make POST request: Exception: API Error: 500 {message: Incorrect syntax near 'EXEC testStoredProcedure @user_id = null, @img_data = '', '.}

According to the logs, the syntax near specifying the parameter for EXEC testStoredProcedure is incorrect. Therefore, what am I missing here?

Answer 1

If none of the given attempted approaches are still not working, even if there's a workaround in it.

Here's the solution that improves readability and provides the same outcomes as well:

Replace this code snippet:

const query = `
    'EXEC testStoredProcedure
    @user_id = ${user_id},
    @img_data = ${img_data},
    @img_size = ${img_size},
    @birthday = ${birthday},
    @country = ${country},
    @email_add = ${email_add},
    @mobile_no = ${mobile_no},
    @key = ${key}'
`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

with this code snippet:

const query = `
    EXEC testStoredProcedure
    @user_id,
    @img_data,
    @img_size,
    @birthday,
    @country,
    @email_add,
    @mobile_no,
    @key`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

You'll notice that req.body variables are removed, and I found out that the single quote (') causes the exception incorrect syntax.

Edit: Optimize the API controller function's structure

This code snippet introduces inappropriate setup:

const query = `
    EXEC testStoredProcedure
    @user_id,
    @img_data,
    @img_size,
    @birthday,
    @country,
    @email_add,
    @mobile_no,
    @key`;

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .query(query);

and it should be refactored into:

const pool = await poolPromise;
const request = pool.request();
const result = await request.input('user_id', sql.NVarChar(50), user_id || null)
    .input('img_data', sql.VarBinary(sql.MAX), Buffer.from(img_data))
    .input('img_size', sql.Decimal(10, 3), img_size)
    .input('birthday', sql.Date, birthday)
    .input('country', sql.VarChar(200), country)
    .input('email_add', sql.NVarChar(150), email_add)
    .input('mobile_no', sql.VarChar(50), mobile_no)
    .input('key', sql.VarChar(100), key)
    .execute('testStoredProcedure');

Remove the query variable because it is redundant, and refactor the .query(query) and change it into .execute('testStoredProcedure').

I hope it helps the future readers! ;)