Support for output parameters in stored procedured

[HeikoR]

Suggest (+1) support for output parameters on stored procedures.

For example if I have

EXEC    @return_value = [user_get_data]
        @user_id = @user_id OUTPUT,
        @user_type = @user_type OUTPUT,
        @login_name = N'heiko'

 where @user_id, and @user_type are int's and @login_name is a varchar

And node syntax could look something like the following:

conn.query( 'user_get_data',  ? , ?, ?, paramArray, function ( err, result ) { ...

where 
    paramArray[0] would be first output param, 
    paramArray[1] would be second output param, 
    paramArray[2] would be input param

or

conn.query( 'user_get_data',  'userID', 'userType', 'loginName', paramObj, function ( err, result ) { ...

where initially
    paramObj['loginName'] = 'heiko'
    paramObj['userType'] = undefined
    paramObj['userID'] = undefined

and after successfull execution
    paramObj['loginName'] = 'heiko'
    paramObj['userType'] = 23424
    paramObj['userID'] = 1

or any other syntax suggestions here ...

Let me know If I can help in any way.

Regards, Heiko

[jguerin]

This is a very good Enhancement request, thanks for putting in so much detail!

[CycoPH]

Would be nice to get output parameters from stored proc calls working.

When doing something like:
conn.queryRaw("{CALL DumpTestTable (?,?)}", [p1, p2], function (err, results) { ...})
and the results are all in, the variables p1 and p2 should be updated with the values.

Would make the whole mssql node interface a real world interface and not just an experiment.

Peter

[jbigelow]

I agree with CycoPH concerning the need for output parameter support. Without an ORM like Entity Framework I had given some thought to using just sprocs for all data access for consistency reasons instead of a mix of ad hoc queries and sprocs but the lack output parameters doesn't make that possible yet. It will be a good addition when added.

[jkint]

What would you guys think if output parameters were returned as a pseudo result set? Instead of updating the passed in variables, the parameters would be returned an 'output_params' event?

The thought is that metadata and other information about the parameters would be available as well.

[CycoPH]

I like that idea. It would also be way simpler to implement with your current code base. We can always write a mapping from the output parameters to the actual parameters in JS.

One thing to remember is the actual return parameter of the stored proc.

[icosahedron]

Great. Thanks for the feedback.

[HeikoR]

Any solution that will allow for output parameters will be welcome. :)

We are currently holding back on porting parts of our current system to node until there is proper support for stored procedures.
(Our current system in implemented in C++ using OLE DB templates that call SQL stored procedures - and we would like to break this system into smaller components - some of which will run as node modules)

[abinop]

This is something that I also find very useful (parameters returned as pseudo result set).
As it is now,calling a stored procedure that returns a result set, how can one read this resultset?

[jguerin]

It should just come back as a normal result set. Are you not seeing this?

[abinop]

Yes, it works ok. The problem was -as I found after a lot of trial & error- that I had an insert statement and then a select one and it was not returning anything. Not until i put "nocount on".

[node2013]

I have some questions:

1. For now on node.js application, is there any way to get output parameters from stored procedure in MS SQL Server? Are there any other node.js packages with this feature, which I can use before releasing newer version of msnodesql? (I know I can use ADO.NET using edge.js, but I'm thinking about way without using CLR)
2. When will be next relase of msnodesql?

[CycoPH]

There is a way to get the output parameters. Declare the SQL similar how the Management Studio would do it:
i.e
DECLARE @return_value int,
@bonus_answer int,
@bonus_reason int

EXEC @return_value = [dbo].[bank_get_all_info]
@user_id = 1,
@bonus_answer = @bonus_answer OUTPUT,
@bonus_reason = @bonus_reason OUTPUT

SELECT @bonus_answer as N'@bonus_answer',
@bonus_reason as N'@bonus_reason'

This last select will make the return variables show up in the JSON structure.

[node2013]

Thanks for answer.

[jkint]

Yes, thank you @CycoPH for the answer.

For now this is our guidance on using output parameters until we implement proper support for them.