Solved

Prevent Duplicate Inserts

Posted on 2013-05-23
10
144 Views
Last Modified: 2013-05-29
Hello Experts,

I have the following stored procedure below. I need to prevent employees from entering into the system based on if they supply a duplicate first name, last name, and or email address.

USE [PACYBER_WellnessTracker_12-13]
GO
/****** Object:  StoredProcedure [dbo].[InsertEmployee]    Script Date: 5/23/2013 2:19:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertEmployee]

(
@emp_firstname varchar(50),
@emp_lastname varchar(50),
@emp_email varchar(100),
@emp_username varchar(50),
@emp_password binary(96)
)

AS

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM  dbo.Employees WHERE emp_firstname = @emp_firstname AND emp_lastname = @emp_lastname AND emp_email = @emp_email AND emp_username = @emp_username)
BEGIN
   INSERT dbo.Employees (emp_firstname, emp_lastname, emp_email, emp_username, emp_password)
   VALUES (@emp_firstname, @emp_lastname, @emp_email, @emp_username, @emp_password) 
COMMIT;

END

ELSE

BEGIN
    RAISERROR('You are already in the system!', 16, 1)  
 ROLLBACK TRAN 
 
 END

Open in new window

0
Comment
Question by:asp_net2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192019
in your stored procedure you are looking for employee user name as well along with the other three mentioned in your question, I just removed and it should work now as you desired

USE [PACYBER_WellnessTracker_12-13]
GO
/****** Object:  StoredProcedure [dbo].[InsertEmployee]    Script Date: 5/23/2013 2:19:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertEmployee]

(
@emp_firstname varchar(50),
@emp_lastname varchar(50),
@emp_email varchar(100),
@emp_username varchar(50),
@emp_password binary(96)
)

AS

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM  dbo.Employees WHERE emp_firstname = @emp_firstname AND emp_lastname = @emp_lastname AND emp_email = @emp_email)
BEGIN
   INSERT dbo.Employees (emp_firstname, emp_lastname, emp_email, emp_username, emp_password)
   VALUES (@emp_firstname, @emp_lastname, @emp_email, @emp_username, @emp_password) 
COMMIT;
END

ELSE

BEGIN
    RAISERROR('You are already in the system!', 16, 1)  
 ROLLBACK TRAN 
 
 END

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 39192145
Ok, that is still not working though :(

I just tested it and if I have duplicate first name, last name, email address BUT have different username the employee gets added.

I need to make sure that neither of first name, last name, email address, and username are duplicate. If either one of those are the same then the insert should fail.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192180
ok let us write your test cases

employee record existing in datbase prior inserting

first name Neo LastName Jarvis email gm.surendra@gmail.com userName neo_jarvis

Now what should happen to the below

first name as Neo Last Name Expert email gm.surendra@gmail.com username neo_jarvis

should this be added or discarded...
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 4

Author Comment

by:asp_net2
ID: 39192221
that should be added.

if first name and last name are the same then discard.
if email is already in the system then discard.
if first name, last name, email, and username are in the system then discard.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39192247
Ok, Then give this a whirl

USE [PACYBER_WellnessTracker_12-13]
GO
/****** Object:  StoredProcedure [dbo].[InsertEmployee]    Script Date: 5/23/2013 2:19:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertEmployee]

(
@emp_firstname varchar(50),
@emp_lastname varchar(50),
@emp_email varchar(100),
@emp_username varchar(50),
@emp_password binary(96)
)

AS

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM  dbo.Employees WHERE (emp_firstname = @emp_firstname AND emp_lastname = @emp_lastname) or (emp_email = @emp_email) or 
(emp_firstname = @emp_firstname AND emp_lastname = @emp_lastname AND emp_email = @emp_email AND emp_username = @emp_username)
)
BEGIN
   INSERT dbo.Employees (emp_firstname, emp_lastname, emp_email, emp_username, emp_password)
   VALUES (@emp_firstname, @emp_lastname, @emp_email, @emp_username, @emp_password) 
COMMIT;
END

ELSE

BEGIN
    RAISERROR('You are already in the system!', 16, 1)  
 ROLLBACK TRAN 
 
 END

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 39192316
Hi,

In your discard cases, the third one is redundant - already covered with same first name and last name.

I suggest that you will need user_name to be unique in your system as well. I can't conceive of duplicate user_name ever being a good thing.

Kind Regards
  David
0
 
LVL 4

Author Comment

by:asp_net2
ID: 39201123
@dtodd,

What do you mean by the "third one is redundant"?

I also need to make sure user_name is unique and NOT duplicated in the system.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 39201124
@Neo_jarvis,

Can you please walk me through what your code will do?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39201190
@asp_net2

My code will check if the first name , last name combination already exists in the database or the e-mail id already exists in the database.
if they exists that it will raise an error or else it will insert

NOTE: It will not check for existance or duplication of the user name.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39202489
Hi,

In the example Neo_Jarvis posted, the second entry had the same username as the first, and you said that it should be added.

The tests read
if first name and last name are the same then discard.
if email is already in the system then discard.
if first name, last name, email, and username are in the system then discard.

I suggest that if the firstname.lastname pair is unique then the firstname/lastname/email/username will also be unique.

Then the test should read
if username is already in the system then discard.

Regards
  David
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
Help with Data Warehouse / Data Marts 4 41
how to solve "recursive" database tables 2 55
Powershell error using sql agent job 24 37
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question