Hello everyone! I am new to SQL Server in Azure functions using Typescript. I am currently facing an issue while trying to update a row in the database using declared variables, particularly with VARCHAR types. Strangely, it works fine in the database tool but when running the query inside the Azure function, I encounter an HTTP 500 error.
RequestError: Incorrect syntax near 'kg'
(working with weight as a string).
Here is my current code:
const trainingId: number = parseInt(req.params.training_id);
const exerciseId: number = parseInt(req.params.exercise_id);
const weight: string = req.body.weight;
await mssql.query(
`DECLARE @sql NVARCHAR(4000);
DECLARE @training_id INT;
DECLARE @exercise_id INT;
DECLARE @weight VARCHAR(255);
set @sql = N'
UPDATE Exercises SET weight = @weight WHERE training_id = @training_id AND exercise_id = @exercise_id'
SET @training_id = ${trainingId};
SET @exercise_id = ${exerciseId};
SET @weight = ${weight};
exec sp_executesql @sql, N'@training_id INT, @exercise_id INT, @weight VARCHAR(255)',
@training_id, @exercise_id, @weight`
I also attempted inserting the weight variable into the query like this:
SET weight ' + @weight + '
But that did not work either.
I have tried different approaches such as referencing a solution here: How to pass parameter to mssql query in node js, but they have not been successful in resolving my issue. Can anyone provide advice on how to correctly pass the parameters without risking SQL injection?
Thank you for any guidance!