0

I have a SQL Server stored procedure to create a new user which I call from my Nodejs code. All works fine and the record is created but I want to be able to check if it really was inserted and was hoping to use the rowsAffected but that always comes back as [] the result from my insert is

{
    "recordsets": [],
    "output": {},
    "rowsAffected": [],
    "returnValue": 0
}

If I call the procedure from SQL Server Management Studio, I get the affected rows of 1. So am I missing something?

Here is code of how I call the stored procedure:

let storedProcedure = async (params, storedProcedureName) => {
    const pool = await getOrCreatePool()
    let request = await pool.request()

    params.forEach((parameter) => {
        parameterDirection = parameter.isOutput ? 'output' : 'input';
        request = request[parameterDirection](parameter.name, parameter.type, parameter.value)
    });

    try {
        return await request.execute(storedProcedureName)
    } catch(err) {
        console.error('StoredProcedure error', err);
        return null;
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MisterniceGuy
  • 1,646
  • 2
  • 18
  • 41
  • Do you happen to have `SET NOCOUNT ON` in your stored procedure? – James Jul 22 '18 at 23:34
  • I will have to check, but why does the ms sql studio report correct rows effected. If no count is on does that not effect the studio queries ? – MisterniceGuy Jul 23 '18 at 02:11
  • Which Node JS package are you using to connect to the database and execute the stored procedure? – Guillermo Gutiérrez Jul 23 '18 at 07:53
  • 1
    @GuillermoGutierrez looks a lot like `mssql` to me – James Jul 23 '18 at 07:58
  • Try to add RETURN @@ROWCOUNT at the end of the stored procedure, and then check `returnValue`. If it is still zero, maybe the stored procedure is not being called with the right parameters. You can try to see how the procedure is being called and executed via the SQL Server Profiler. – Guillermo Gutiérrez Jul 23 '18 at 08:16
  • @MisterniceGuy that question has already been [answered](https://stackoverflow.com/a/5884333/82586), did you manage to check if `SET NOCOUNT ON` was set in the SP? – James Jul 23 '18 at 10:04
  • Yes i checked and by default it is set to nocount on. Removing that returns the rowcount. But what confused me is that MS Sql Studio returned the effected rowcount even when set to NOCOUNT on – MisterniceGuy Jul 23 '18 at 14:27

0 Answers0