Getting values into VB from an Output parameter of a stored procedure

Tech-Archive recommends: Speed Up your PC by fixing your registry

Dear friends,
Working in the VB6 environment
I have a stored procedures on sql 2000 which has both input & output
parameters. With this SP I intend to insert into certain tables, and if an
error occurs during execution of the any of tsql commands sp generates an
error into an output parameter called @error. Construct is like follows:
Create Sp XXX
@P1
@P2 = default
... etc.
@Error Out
AS
Begin Tran
T-SQL Statements
Commit Tran
SELECT @Error=0
Return
HandleError:
RollBack Tran
Select @Error = @@Error
Return

Because some of the input parameters have default values and I do not know
exatly if a parameter is supplied by the user or not, I prefer to use named
parameter format in VB
When I construct my parameter string in the form of @P1= value, @P2 = value,
.... I use the execute method of connection object to run the SP.
Assuming that Cnn is the connection object I issue the following code from
VB

Cnn.Execute XXX & Parameter string

However in this format I am not sure how do I include output parameter or
get the value of it after cnn executes stored procedure. Because if I do
not provide any value for the output parameter (which is quite normal as I
am expecting sth from it) SQL Server complains about it.

Any help is very much appreciated.

Ömer Ayzan




.

Quantcast