0

How do i execute a stored procedure created in a Microsoft SQL Server Database in my application created on Node.js express framework which uses MSSQL package provided by Node.js to connect to the DB and execute DB related tasks?

Please be clear on how to pass the parameters and how the stored procedure name bares a reference in the application through mssql package.

I am quite new to the technology so any help would be greatly appreciated. Thank you.

Niranjan A
  • 95
  • 2
  • 8
  • Possible duplicate of : https://stackoverflow.com/questions/38909920/running-a-stored-procedure-with-nodejs-and-mssql-package-error Please take a look. – Udit Solanki May 25 '18 at 05:15
  • @UdItSolanki I checked that link but i found the answers very unclear. They do not clearly mention how to pass the parameters nor do they explain how the request object can directly refer to the sp through the execute() method. Hence asking the question. – Niranjan A May 25 '18 at 06:12

1 Answers1

2

There are many ways of doing this and many packages that can help. I would recommend the Knex.js package. Once you've set that up and made a connection, you can then use the knex.raw function to execute arbitrary SQL and have it returned as a knex object. I'm not sure of the specific SQL syntax for MSSQL, but it should be very similar to Postgres where you would do something like:

knex.raw('select * from my_func(?, ?)', [valOne, valTwo]);

In the above example I am running a select query against a stored procedure called my_func. I am them passing in a question mark for each parameter, and then matching those up in an array after the string. This will result in the SQL being executed.

select * from my_funct(valOne, valTwo);

This includes escaping values to help defend against things such as SQL injection.

Your execution syntax may be slightly different in MSSQL, but you can still use knex.raw and the question mark + array syntax to inject values into a prepared statement like this.

Elliot Blackburn
  • 3,759
  • 1
  • 23
  • 42