i am trying to execute a stored procedure in MSSQL Server(Win2000).
The Stored Procedure has 2 output variables. The stored procedure runs ok from the osql prompt but i want to execute the stored procedure from a C program. When i run the C program, i get the following error :

"Must declare the variable @dbspi_error."

Here is the stored procedure definition :

USE master
DROP PROCEDURE pi_test_proc
GO
CREATE PROCEDURE pi_test_proc @pi_error varCHAR(500) OUTPUT, @pi_value INT OUTPUT
AS
set @pi_error='no_error'
select @pi_value=uid from sysobjects where name='sp_helpsort'
RETURN
GO

Here is the test run from the osql prompt which works ok :

declare @pi_error_output varchar(500)
declare @pi_value_output varchar(500)
execute pi_test_proc @pi_error = @pi_error_output OUTPUT, @pi_value = @pi_value_output OUTPUT
print 'pi_error is ' + convert(varchar(100),@pi_error_output)
print 'pi_value is ' + convert(varchar(100),@pi_value_output)
go



Now, when i try to run the following command
execute pi_test_proc @pi_error = @pi_error_output OUTPUT, @pi_value = @pi_value_output OUTPUT
as embedded sql in a C program, it complains.

Here is the snippet of the C code that i am running :

EXEC SQL BEGIN DECLARE SECTION;
char *sql_code;
int dbspi_value;
double tmp_threshold;
static char dbspi_error[8000];
EXEC SQL END DECLARE SECTION;

dbspi_value = 0;
dbspi_error[0] = 0;

sql_code = "execute pi_test_proc @pi_error = @pi_error_output OUTPUT, @pi_value = @pi_value_output OUTPUT";
EXEC SQL PREPARE SQL1 FROM :sql_code;


EXEC SQL EXECUTE SQL1 USING :dbspi_error, :dbspi_value;

if u need more info on this, please let me know.

i have tried to find an example of what i am trying to do in the SQL server documentation but have failed.


regards
-sarab