I have this sample stored procedure(below) for inserting data in a table. Basically, in this query, it will NOT insert the data if the employee ID already exists. My code works fine in VB6, I was able to insert record using the stored proc, but I don't know how to check if the record already exists. Please help me how to do it in VB6.
ALTER PROCEDURE [dbo].[SP_EMPLOYEE_INSERT]
@emp_id nvarchar(10)
,@emp_lname nvarchar(50)
,@emp_fname nvarchar(50)
,@emp_initial nvarchar(2)
,@emp_mngr_id nvarchar(10)
,@inputDt datetime
AS
BEGIN
IF NOT EXISTS (SELECT TOP (1) EmpID FROM EMPLOYEE WHERE EmpID = @emp_id)
BEGIN
INSERT INTO Employee
(EmpID, Emp_Lastname, Emp_Firstname, Emp_MidInitial, EmpMngrID, InputDate)
VALUES
(@emp_id, @emp_lname,@emp_fname, @emp_initial, @emp_mngr_id, @inputDt)
SET @emp_id = @@IDENTITY
END
ELSE
BEGIN
SET @emp_id = (SELECT TOP (1) EmpID FROM EMPLOYEE WHERE EmpID = @emp_id)
RETURN @emp_id
END
END