I use this on signup forms to prevent a user from registering a second account with the same email address.
CREATE Procedure sp_AddSubscriber
@Name as nvarchar(50),
@Email as nvarchar(50),
@AddSubscriber varchar(255) OUTPUT
AS
IF (SELECT COUNT(Email)
FROM TSubscribers
WHERE Email = @Email) = 0
BEGIN INSERT TSubscribers
(Name, Email) Values (@Name, @Email)
SET @AddSubscriber = "True"
END
ELSE
SET @AddSubscriber = "The email address you have entered has already been registered in our database."
GO