Solved

Prevent Duplicate Inserts

Posted on 2013-05-23
10
145 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

689 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