How to return a scalar value from MSSQL in Node js?

Question

I have a stored procedure in MSSQL which determines the COUNT of incidents in a year which is passed to the stored procedure as a parameter. I try to access this scalar value in my node JS backend, but it returns a zero, while in the SSMS it gives the correct value. What am I doing wrong?

The Stored Procedure:

CREATE PROCEDURE Read_IncidentYearCount_SP
    (
        @Year INT
    )
AS
    SELECT COUNT(IncidentDate) FROM Incidents
    WHERE YEAR(IncidentDate) = @Year
        
GO

The Node JS javascript:

const year = new Date().getFullYear();    
console.log('Year = ' + year);

const result = await pool.request()
    .input('Year', year)
    .execute('Read_IncidentYearCount_SP');

console.log('Result = ' + result.returnValue);

Answer 1

To access the scalar value returned by the SELECT COUNT(IncidentDate) statement in your stored procedure, you need to modify your code

const year = new Date().getFullYear();    
console.log('Year = ' + year);

const result = await pool.request()
    .input('Year', year)
    .execute('Read_IncidentYearCount_SP');

const count = result.recordset[0][''];
console.log('Result = ' + count);

In the modified code, the count variable retrieves the scalar value from the first row (result.recordset[0]) and the empty string property (['']), as the scalar value doesn't have a specific column name. Then you can log the count value to verify that it matches the expected value.

Make sure you have imported the mssql package and established a connection to the MSSQL database correctly before executing this code.