nodejs mssql - Too many parameters passed Stored procedure

Question

I running into "Too many parameters passed" to stored procedure while trying to run a stored procedure in NodeJs using mssql.

Code:

//This computerName is what we'll find in our mssql server to see
//if the server entry exist or not and the stored procedure will take this as a parameter.
var computerName = "some.fake.server.com";
var secProfile = "";

//Logic
// If computerName passed is valid and not null.
//if (computerName != "") {
var sql = require('mssql');

var config = {
    user: 'dbuser',
    password: 'secure9ass',
    server: 'dbserver.domain.com',
    database: 'DBName',
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
}

sql.connect(config).then(function(output) {
  // Stored Procedure
    new sql.Request()
    .input("ComputerName", sql.VarChar(100), computerName)
  .output('sqlOutput', sql.VarChar(1000), "Stored procedure has not run yet!!")
    .execute('dbo.getSysStatus_ByName').then(function(recordsets) {
    console.dir(recordsets);
  }).catch(function(err) {
        // ... error checks
    console.log('ERROR1::: ' + err)
    console.log("----")
    console.log(err)
    console.log("====")
    console.log(recordsets)
    console.log("----")
    console.log('ERROR2::: '+ sqlOutput);
    console.log('ERROR3::: '+ request.parameters.sqlOutput.value);
});
  console.log(output);
}).catch(function(err) {
  // ... error checks
  console.log('ERROR5::: '+ err);
});

Error: Too many parameters passed to procedure name: getSysStatus_ByName

I checked in the Database that the stored procedure has only one parameter that it's expecting and the parameter is: ComputerName

I know I'm missing just the name of the parameter but I have tried

.input("@ComputerName", sql.VarChar(100), computerName)

or

.input("computerName", sql.VarChar(100), computerName)

or

.input("computername", sql.VarChar(100), computerName)

Nothing worked and gives me the same error. Also, tried to change the parameter type from sql.VarChar(xxx) to sql.Int (in this case, it errors out saying invalid type so I know sql.VarChar(xxx) is good.

One of the .vb (visual basic) script which runs successfully have the following code lines and it works. I'm wondering why my code in nodejs is giving me the error.

                      Set objCmd = CreateObject("ADODB.Command")

                      ObjCmd.ActiveConnection = Conn

                      ObjCmd.CommandTimeout  = 180 'in seconds

                      ObjCmd.CommandType = 4          'Stored Procedure

                      ObjCmd.CommandText = "dbo.getSysStatus_ByName"

                      objCmd.Parameters.Append objCmd.CreateParameter("@ComputerName", 200, 1, 1024, ComputerName)

As per CreateParameter (ADO help page), it says, 200 is the # for

adVarChar 200 A string value (Parameter object only). 

1 means: direction variable (where 1 is for an Input Parameter in my case) and
1024 is the size of the input variable.

I don't have VPN connection to try but I hope the error is not coming due to 1024 vs 1000 size (in my code example for .input(..) line.). I'll test it tomorrow.

what if you remove the .output part !!!! and run execute after providing input params — Malcolm, Jun 17 '16 at 09:10
@Malcolm I don't think that'll change the behavior but I'll try that and let you know. — AKS, Jun 17 '16 at 10:29
@Malcolm OK, commenting the .output(...) line fixes the issue. I checked that the stored procedure (if run manually using SQL Manager), runs successfully and returns 0 (Integer). But, I'm wondering WHY I can't give: .output('sqlOutput', sql.Int) or something like .output('return_value', sql.Int) .... as soon as I uncomment that line, I see "Too many parameters". Wondering if I even need to define .output(...) line as I think I can also do like this: .execute('dbo.getSysStatus_ByName').then(function(err,recordsets, returnValue) { .... } — AKS, Jun 17 '16 at 16:00
If you specify the OUTPUT keyword for a parameter in the procedure definition than you don't need to comment out the output part , it will work . if your execution of procedure is returning an output you will get in to recordsets ..the function which you already implemented. — Malcolm, Jun 20 '16 at 06:14
@Malcolm - Can you post your replies as an answer, I'll mark it as a valid answer. Thanks. — AKS, Jun 20 '16 at 22:53

score 2 · Accepted Answer · edited Oct 01 '16 at 08:07

If you specify the OUTPUT keyword for a parameter in the procedure definition than only you get the privilege to use the below line; when you are defining sql.Request()

.output('sqlOutput', sql.VarChar(1000), "Stored procedure has not run yet!!")

Using OUTPUT type in stored procedure just for an example considering your case:

CREATE PROCEDURE dbo.getSysStatus_ByName    
    @ComputerName varchar(100),  
    @sqlOutput VarChar(1000) OUTPUT  
AS 
BEGIN 
    //YOUR SP CODE HERE 
END

If your stored procedure doesn't have the OUTPUT parameter and it simply returns the recordsets you can fetch the same via function callback :

   .execute('dbo.getSysStatus_ByName').then(function(recordsets) {
    //recordsets is an result return by your executed stored procedure  })

SQL Server with NODE.JS - Get started

As in your case your stored procdeure doesn't contain any Output type so you can simply remove /comment out below line :

.output('sqlOutput', sql.VarChar(1000), "Stored procedure has not run yet!!")

one quick question, using crypto, or similar, how can I make the above code in my post secure i.e. I don't hard code the config . password in it? Any helps! — AKS, Jun 29 '16 at 21:55

nodejs mssql - Too many parameters passed Stored procedure

1 Answers1

Linked