Solved

Prevent Duplicate Inserts

Posted on 2013-05-23
10
140 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
  • 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now