0

When I attempt to pull data from the database I am getting zero results although my parameter is correct.

From my index.js where CustID is a parameter

app.get('/contacts', (req, res) => {
    actions.getConts(req.params.CustID).then((data) => {
        res.json(data[0]);
    })
})

From my operations.js with GetContNT being a stored procedure on the server

async function getConts(CustID) {
    try {
        let pool = await sql.connect(db);

        let contList = await pool.request()
            .input('CustID', sql.NVarChar, CustID)
            .execute('GetContNT');
        return contList.recordsets;
    }
    catch (error) {
        console.log(error);
    }
}

module.exports = {
    getConts: getConts,
}

If I run the stored procedure by itself on the SQL Server I get results.

If I replace the parameter with the text value, I get results.

With the parameter I get [] ... no results

I tried replacing the procedure call .execute('GetContNT'); directly with a query

.query("SELECT * FROM Contacts WHERE CustID = @CustID");

but got the same results.

Appreciate your help.

14
  • Given the limited information, cannot reproduce with [email protected] against SQL Server 2022. Have you double checked the db config object to confirm that it's connecting to the correct server, instance and database? Commented Apr 27 at 8:12
  • 1
    I would suggest a simple debug with console.dir(contList.recordsets) to confirm that it's completely empty. i.e.: if your stored procedure performs any delete/insert/update operations before it performs the select operation then Node will be seeing record counts with empty results for each of those data modification operations. Try inserting SET NOCOUNT ON; at the beginning of your stored procedure to see if it makes a difference. Commented Apr 27 at 8:16
  • I also vote for SET NOCOUNT ON; being the solution, although i have no idea why every "orm" library mishandles this part of sql server support, it should work with or without it Commented Apr 28 at 9:24
  • Hi, and thanks for the suggestions. I tried the NOCONT but did not work. I tried without the stored procedure but same result. Here is the code in full. Commented Apr 28 at 22:09
  • 1
    You can Edit your question to replace the code it already contains, or supplement it with newer versions or additional information. As you've discovered comments are not really the place for such things. Commented Apr 29 at 3:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.