RETURN statement

Transact-SQL

john565218


I want to end the stored procedure if duplicate record is found.
What would be the best way to do it In the calling program VB6,
I would then like to capture the value 1 to display duplicate message.
Thanks.



IF EXISTS ( SELECT e.EmployeeID
FROM Employee e
WHERE
e.EmployeeID = @NewEmployeeID )

DROP dbo.#NewEmployee
RETURN 1

ELSE
...
...
END

Top

Re: RETURN statement

Konstantin Kosinsky


If you want to read return value of stored procedure in VB6, you could define one of command parameters with parameter direction to RETURN, then just read it after query execution.



Top

Re: RETURN statement

Manivannan.D.Sekaran

It is best approach to return the control using return.

following VB code will help you to get the return value,

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






Top