Close. The Return parameter contains the value returned by a RETURN
statement in your SP. If you do not have a RETURN statement, a successful
procedure will return 0, while a procedure that raises an error will return
NULL.

There are 3 ways to return values from a SQL Server stored procedure:
1. a Select statement that returns a resultset
--run this script in Query Analyzer (QA):
Create Procedure SelectValue
(@input    int)
AS
Select @input + 5
go
exec SelectValue 10
go
drop procedure SelectValue

2. a Return parameter:
--run this script in QA:
create procedure ReturnValue
(@input    int)
AS
Return @input + 5
go
declare @returnvalue    int
exec @returnvalue = ReturnValue 10
select @returnvalue
go
drop procedure ReturnValue

3. an Output Parameter:
--run this script in QA:
create procedure OutputValue
(@input    int output)
AS
SET @input = @input + 5
go
declare @outputvalue int
SET @outputvalue = 10
exec  OutputValue @outputvalue output
select @outputvalue
go
drop procedure OutputValue

I do not recommend method 1 for returning a single value. A resultset is
expensive to build, in that it must contain metadata in addition to data. So
more network traffic is created, and the client app needs to expend more
resources in order to retrieve and expose the resultset to the calling
procedure.

Most developers use the Return parameter to return status codes instead of
data. This is for the sake of consistency: there is no technical reason not
to use RETURN to return data, except that RETURN can only be used to return
integers. If you need to return other datatypes, you need to use an output
parameter.

Very strange. Did you have a unique index to prevent duplicate inserts?

Yes, but you don't have to. You can use EXISTS in your stored procedure to
do this without raising an error:

IF NOT EXISTS
    (Select * from sometable
    where somecolumn=<data_to_be_inserted>)
BEGIN
    INSERT sometable ...
END
--optionally - do this only if you want your client app to know
ELSE
BEGIN
    RETURN 2
    --code which you create to designate that record exists
END

HTH,
Bob Barrows