How to Duplicate Email Id block from Database

Vikas

8y1.1k

CREATE PROCEDURE [dbo].[sp_user_master_add]

@Email NVARCHAR(100),

@Password NVARCHAR(100),

@Name NVARCHAR(250),

@Company_Name NVARCHAR(500),

@Address1 NVARCHAR(1000),

@Address2 NVARCHAR(1000),

@City NVARCHAR(150),

@State NVARCHAR(150),

@Pincode INT,

@Mobile NVARCHAR(15),

@key NVARCHAR(150),

@Return_Value INT OUTPUT

SET @Return_Value = 0

BEGIN TRAN

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

INSERT INTO

tbl_user_master

(

Email,

[Password],

Name,

Company_Name,

Address1,

Address2,

City,

[State],

Pincode,

Mobile,

[key]

)

VALUES

(

@Email,

@Password,

@Name,

@Company_Name,

@Address1,

@Address2,

@City,

@State,

@Pincode,

@Mobile,

@key

)

DECLARE @NewID DECIMAL

SELECT @NewID = SCOPE_IDENTITY()

DECLARE @new_image NVARCHAR(250)

SET @Return_Value = @@ERROR

IF @Return_Value <> 0

BEGIN

ROLLBACK TRAN

RETURN @Return_Value

END

ELSE

SET @Return_Value = @@ERROR

IF @Return_Value <> 0

BEGIN

ROLLBACK TRAN

RETURN @Return_Value

END

ELSE

BEGIN

COMMIT TRAN

RETURN @Return_Value

END

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
first lets look at the SP
-- Assuming that you will UNIQUE-INDEX the email field you will not need to do any BEGIN TRANS anD ROLL BACK

-- You must have a TRY and CATCH insted of BEGIN TRANS anD ROLL BACK
-- You have 2 IF statement that rae the same, why? Line 6 and line 14

-- line 3 does nothing so i commented it out

Line 4 and 12 you care capturing the ERROR twice , why??????

NOW ABOUT THE SOLUTION:
the question is not clear, but in general i would just put an UNIQUE INDEX on that field, NOT PRIMARY KEY
DECLARE @NewID DECIMAL
SELECT  @NewID = SCOPE_IDENTITY()
--DECLARE @new_image NVARCHAR(250)
SET @Return_Value = @@ERROR

IF @Return_Value <> 0
    BEGIN
        ROLLBACK TRAN
        RETURN @Return_Value
    END
ELSE
    SET @Return_Value = @@ERROR

IF @Return_Value <> 0
    BEGIN
        ROLLBACK TRAN
        RETURN @Return_Value
    END
ELSE
    BEGIN
        COMMIT TRAN
        RETURN @Return_Value
    END
+1
Sankara Krishnan Venugopal8y ago
You have two options to stop allowing to insert duplicate email ID in column.

1. Set the primary key for Email column
2. Use the select statement in your code to check, if the email ID exist or not.

Hope this helps.
+1
Ravi Kiran Chanduri8y ago
Hi Vikas,

Could you explain your requirement bit clear using am example?

If you don't want to insert duplicate email ID you can setyour column as Unique key.
+1
Packiaraj Santhiyagu8y ago
Hi Vikas,

If you want to avoid duplicate on inserting, Just set your column as primary/unique key.If you try to insert duplicate value, it will throw the error you can catch with @@Error.

If you want to avoid on retrieval, just group by your column name.
+4
Uttam Chaturvedi8y ago
Hi Vikas,

I would recommend to be more clear with your question and if necessarary give examples stating your issue.
0