nodejs, express call MS SQL Server Procedure with parameters

npm install express tedious

npm install cors

nodejs database.js

 const express = require('express');

const { Connection, Request, TYPES } = require('tedious');

const cors = require('cors');

const app = express();

// Enable CORS for all routes 

app.use(cors());

const port = 3000;

const config = {

  server: '192.168.2.70', // Update with your server details

  authentication: {

    type: 'default',

    options: {

      userName: '***', // Update with your username

      password: 'Le*****', // Update with your password

    },

  },

  options: {

    encrypt: false, // If you're on Microsoft Azure

    database: 'sample_data', // Update with your database name

  },

};

app.get('/api/olympic-winners', (req, res) => {

  const country = req.query.country;

  const year = parseInt(req.query.year, 10);

  console.log("Country is " + country)

  console.log("year is " + year)

  if (!country || isNaN(year)) {

    return res.status(400).json({ error: 'Country and year parameters are required' });

  }

  const connection = new Connection(config);

  connection.on('connect', (err) => {

    if (!err) {

      console.log('Connected');

      executeStatement(connection,country, year, res);

    } else {

      console.error('Error connecting:', err.message);

      res.status(500).json({ error: err.message });

    }

  });

  connection.connect();

});

function executeStatement(connection, country, year, res) {

  const request = new Request(`GetTopOlympicWinners`, (err) => {

    if (err) {

      console.error(err);

      res.status(500).json({ error: err.message });

    }

  });

  request.addParameter('Country', TYPES.NVarChar, country);

  request.addParameter('Year', TYPES.Int, year);

  const results = [];

  request.on('row', (columns) => {

    const result = {};

    columns.forEach((column) => {

      result[column.metadata.colName] = column.value;

    });

    results.push(result);

  });

  request.on('requestCompleted', () => {

    console.log(`${results.length} rows returned. requestCompleted`);

    res.json(results);

    connection.close();

  });

  connection.callProcedure(request);

}

app.listen(port, () => {

  console.log(`API server listening at http://localhost:${port}`);

});