get return value from stored proc

How can I get the return value from stored procedure?
Basically, if employee already exists in the following sp, I would like to get the ReturnCode -1 in VB 6 app and display "employee already exists" message. Or, I would like to raise an error in sp which can be displayed in VB client app. How can I do this? Thanks.

hr_addNewEmployee:

DECLARE @.lReturnCode INT
IF EXISTS ( SELECT e.EmployeeID
FROM Employee e
WHERE
e.EmployeeID = @.NewEmployeeID)
BEGIN
SELECT @.lReturnCode = -1
RETURN @.lReturnCode
END

ELSE
...
...
...
RETURN @.lReturnCode

Pretty easy:

declare @.returnValue int
execute @.returnValue = procedureName
select @.returnValue

there is also a way to get that info from the ADO.NET, but you would want to go to the ADO.NET forums for that info

To raise an error just add:

Raiserror ('your error here',16,1)

That is what I would probably do, though for your error, if you are inserting a single employee. just insert the row and let the constraint get it. Same amount of work to check existence but when the work is done, you just have to add the row to the physical structures, rather then do it again.

The duplicate row error will tell you that you have a duplicate (it will always be the same error number) so you can catch it on the client side, or in 2005 you can use a try...catch and look at the ERROR_NUMBER() function and get that value.|||

Yes you can...

Code Snippet

Dim oconn As New ADODB.Connection
oconn.ConnectionString = "{Your Connection STRING}"
oconn.Open

Dim ocmd As New ADODB.Command
Set ocmd.ActiveConnection = oconn
ocmd.CommandType = adCmdStoredProc
ocmd.CommandText = "dbo.testReturn"

Dim param As New ADODB.Parameter
param.Direction = adParamReturnValue
param.Type = adInteger
ocmd.Parameters.Append param

ocmd.Execute

MsgBox param.Value