Parameters collection with OUTPUT parameter

[rac2]

Using ASP with SQL Server 7.

It seems that an OUTPUT parameter value cannot be obtained in the same call with a recordset. Is this true or am I missing something?

Here is the stored procedure.

CREATE PROCEDURE [proc_create_users_list_and_count] (
	@how_many INT OUTPUT
)
 AS

SELECT	user_id,
	first_name,
	last_name,
	user_or_admin
FROM	users

SELECT @how_many = COUNT(*) FROM users

Here is the ASP script that gets the recordset and the OUTPUT parameter value.

var cmdUsers = Server.createObject("ADODB.Command");
cmdUsers.activeConnection = conn; //conn is defined elsewhere.

//Retrieve number of users with an OUTPUT parameter.
cmdUsers.commandText = "{CALL proc_create_users_list_and_count(?)}";

var adInteger = 3;    //type
var sizeBytes = 4;    //size
var adParamOutput = 2; //direction

var parmUserCount = Server.createObject("ADODB.Parameter");
parmUserCount.Type = adInteger;
parmUserCount.Direction = adParamOutput;
parmUserCount.Size = sizeBytes;
cmdUsers.parameters.Append(parmUserCount);

rsUsers = cmdUsers.execute();

var userCount = parmUserCount.value;

This leads to undefined value for userCount. But executing the command without a recordset like this

cmdUsers.execute();

returns a value for userCount.

Is this the way the Parameter object is supposed to work?
Thanks,
Richard

 

[Niv3k]

try putting

SELECT @how_many = COUNT(*) FROM users

above the other SELECT statement in the stored procedure. Or, just have the

SELECT    user_id,
    first_name,
    last_name,
    user_or_admin
FROM    users

be a seperate view, not a stored proc.


Kevin

 

[rac2]

Hello Kevin,

That was a good idea but whether the OUTPUT parameter is first or last the behavior is the same.

Thanks for thinking about this.

 

[culshaja]

I have had this problem.

I solved it by returning the value in the stored proc:

RETURN @how_many

You need to have the first parameter in the parameters collection set to be an adInteger, and have its name set to RETURN_VALUE. I think you set it to be adParamOutput aswell.

The important part is that it has to be the first parameter that is appened to the parameters collection.

Hope this helps.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305