Solved

Prevent Duplicate Inserts

Posted on 2013-05-23
10
143 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 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