How to use parameters in SQL Server?

Question

Beginner here! I just started working with SQL Server in Azure functions (in Typescript). I am trying to update a row in the database using declared variables but I am failing to do so with VARCHAR types. Interesting is that in the database tool it is working fine but once I try to run the query inside the Azure function, I get an http me 500 error

RequestError: Incorrect syntax near 'kg'

(working with weight as a string).

At the moment, my code looks like this:

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 have also tried the syntax where I insert the weight variable into the query like this:

 SET weight ' + @weight + '

and did not work either.

I have also tried to use this approach here: How to pass parameter to mssql query in node js But it did not work for me either. So how can I actually pass the parameters correctly? I am trying to avoid SQL injection.

Thank you so much for any advice!

what is the data type of the column weight in your table, seems that is one of the numeric type, but your request body has the suffix 'kg' which throws the error. — Anand Sowmithiran
– Anand Sowmithiran, Commented Jan 20, 2023 at 13:26

Hans Keﬆing · Accepted Answer · 2023-01-20 13:26:10Z

1

With SET @weight = ${weight}; you are injecting values into a query that you use to execute another query. Why not use that UPDATE query directly after you use mssql.input(...) to set the parameter values (as per your link)?

As I understand it, it should be something like:

const trainingId: number = parseInt(req.params.training_id);
const exerciseId: number = parseInt(req.params.exercise_id);
const weight: string = req.body.weight;

// set up parameters
mssql.input('training_id', Sql.Int, trainingId);
mssql.input('exercise_id', Sql.Int, exerciseId);
mssql.input('weight ', Sql.VarChar, weight);

// execute just the UPDATE query, using the parameters
await mssql.query('UPDATE Exercises SET weight = @weight WHERE training_id = @training_id AND exercise_id = @exercise_id');

answered Jan 20, 2023 at 13:26

Hans Keﬆing

39.5k10 gold badges84 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

boolie29 · Accepted Answer · 2023-01-20 17:40:38Z

1

Perfect! I was doing it wrong the whole time as I was not linking the new Request to the mssql. What works for me now is:

const request: mssql.Request = new mssql.Request();
request.input('training_id', mssql.Int, trainingId);
request.input('exercise_id', mssql.Int, exerciseId);
request.input('weight ', mssql.VarChar, weight);
const result = await request.query('UPDATE Exercises SET weight = @weight WHERE training_id = @training_id AND exercise_id = @exercise_id');

Thanks a lot for the help!

answered Jan 20, 2023 at 17:40

boolie29

556 bronze badges

Collectives™ on Stack Overflow

How to use parameters in SQL Server?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related