Calling a stored procedure with a output parameter

  • Thread starter Thread starter kkbullen
  • Start date Start date
K

kkbullen

I've been testing a stored procedure that has an output parameter.

When I add parameters to the sqlcommand this way, the output parameter
returned is always what I pass in:
With InsertLeg
.Transaction = mySQLTrans
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@Load_Num", SqlDbType.Int))
.Parameters.Add(New SqlParameter("@RC", SqlDbType.Int, 0,
ParameterDirection.Output))
End With

If I declare the sqlcommand this way, it works, the output parameter
from the stored procedure is returned in the @RC parameter.
With InsertLeg
.Transaction = mySQLTrans
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@Load_Num", SqlDbType.Int))
Dim Param As New SqlParameter("@RC", SqlDbType.Int)
Param.Direction = ParameterDirection.Output
.Parameters.Add(Param)
End With

Can someone tell me why these declarations are treated differently?
Thanks,
Kevin
 
Frankly, I don't use either syntax to build the Parameters collection, I use
the Constructor.

cmd.Parameters.Add("@Outparm", SqlDbType.Int).Direction =
ParameterDirection.Output

I would also turn on the SQL Profiler to see what's being sent for each
syntax. It would also help to see the stored procedure. Is this "RC" really
the RETURN value?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Back
Top