What do you want to happen in case where it's a duplicate? You can indicate that there was a duplicate by returning not 0 value or add one more ouput parameter and return that info there.

>I have the following sproc. I want to prevent duplicate Company records.
>
>The problem with this procedure is that if the CompanyName passed in already exists, all that
>happens that the company does not get added.
>
>Is there a better way to do this?
>
>

>ALTER PROCEDURE [dbo].[sp_AddCompany]
>	@CompanyName	VARCHAR(60),
>	@StartDate		DATETIME = NULL,
>	@CompanyId		INTEGER OUTPUT
>
>AS
>
>	DECLARE @CompId INTEGER
>
>	IF @StartDate IS NULL
>		SET @StartDate = GETDATE()
>
>	SELECT @CompId = Rec_Id
>		FROM CompanyInfo
>		WHERE CompanyName = @CompanyName
>
>	IF @CompId IS NULL
>		BEGIN
>
>			INSERT INTO CompanyInfo
>				(CompanyName, StartDate)
>				VALUES
>				(@CompanyName, @StartDate)
>
>			SELECT @CompanyId = SCOPE_IDENTITY()
>
>		END
>