Retreiving return value from stored procedure in C++

[rick7423]

I have a stored procedure on a MSSQL2K server that will perform 2 processes. I am doing a check of the processes and will return an int value which specifies whether the processes completed properly, i.e.
return val - Meaning
0 - both processes failed
1 - the first process completed but the second failed
10 - the second completed but the first failed
11 - indicates both passed

The SP uses the return keyword as in:

USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'updatedata' AND type = 'P')
DROP PROCEDURE updatedata
GO
USE myDB
GO
CREATE PROCEDURE updatedata
@n1 decimal,
@n2 decimal,
@n3 varchar(25),

AS

set nocount on

DECLARE @m1 int
DECLARE @m2 int

... do initial process ....

select @m1 = (select count(*) from table
where params match)
// @m1 should = 1

... do second process ...

select @m2 = 10 * (select count(*) from table
where params match)
// @m2 should = 10

return (@md + @mdh)
// should return 11 to calling app

GO



using following the TSQL string in query analyzer returns the results correctly:

declare @val int
exec @val = updatedata 101002, 105041, 'some part number'
select @val as 'val'


In VC/C++, I am using ADO and the open function to call the SP. I need to know how to format the syntax so the return value is returned to be evaluated.

Considering using this function:

// RS1 is the recordset object
char rsSQL[256];
sprintf(rsSQL,"exec updateData %d, %d, %s", (long)tme, (long)jdt, cmLITM);
RS1->Open(rsSQL, vtMissing, adOpenKeyset, adLockBatchOptimistic, -1);

How can I change this to get the data returned correctly?

Thanks in advance.

[hspc]

Hi
If you don't want to change alot of code .. jsut try to change :
return (@md + @mdh)
to
Select (@md + @mdh) as retcol

then get the value using the recordset as you do in any select statemnt.,

the other way (the better way i think) is to use Command object and add a parameter of return type. and get the value in it.

[rick7423]

This is where I am confused, the SP uses the 'set nocount on' in order to suppress the "# records affected" messages. I am not even sure that this is needed, I am looking at examples and reading tryng to find the best methods.

Using query analyzer, just calling the updateData SP does not return any value, just states "The command(s) completed successfully". I have to use the format:

exec val=updateData val1, val2, val3

in order for the integer to be returned INTO the val variable or for the value top be seen at all. Note that val needs to be passed in with the command string.

This is the crux of my question, using the RS1->Open(...) how do I get the SP to return the integer in to the passed variable? The way I have it setup is wrong, and I can't figure out how to make it right.

Can you jot down the modified SP and also the open method command you are referring to? i will look into the command object method as well, but this should work this way.
This may be really simple, but I get these mental blocks from time to time and can't see the forest because of all the trees.

Thanks in advance.