Duplicate Record Added with Single Insert

Question

Post reply

Duplicate Record Added with Single Insert

gregory.sears

SSC Rookie

Points: 36
More actions
June 23, 2008 at 10:21 am

#93326

Hi, I am new here but I am desperate for what is causing my latest trial. I am trying to insert a single record using a stored procedure however when I run the procedure two records are added. My stored procedure is below. You'll notice that I return the rowcount. When I test the return value it says 1 but there are two records. PLease help

CREATE PROCEDURE insertIndividual

@ID int OUTPUT,

@UserName varchar(100) = Null,

@Password varbinary(128) = Null,

@FamilyID int = 0,

@LastName varchar(100) = Null,

@LastNameView bit = 1,

@FirstName varchar(100) = Null,

@FirstNameView bit = 1,

@MiddleName varchar(100) = Null,

@MiddleNameView bit = 1,

@DOB smalldatetime = Null,

@DOD smalldatetime = Null,

@BirthLocationID int = 0,

@Gender bit = Null,

@BirthInfoView bit = 1,

@Address varchar(100) = Null,

@AddressLocationID int = 0,

@ZipCode varchar(20) = Null,

@RelationshipID int = 0,

@BloodRelated bit = Null,

@PersonalInfoView bit = 1,

@PictureURL varchar(300) = Null,

@WebsiteURL varchar(300) = Null,

@Tested bit = Null,

@TestedDate smalldatetime = Null,

@DiagnosisTypeID int = 0,

@DiseaseID int = 0,

@DiagnosedDate smalldatetime = Null,

@TransplantDate smalldatetime = Null,

@TransplantHospital varchar(100) = Null,

@TransplantLocationID int = 0,

@ScienceInfoView bit = 1,

@FamilyOwner bit = 0,

@CanChangeFamilyInfo bit = 0,

@LastIP varchar(15) = Null,

@Active bit = 1

AS

SET @ID =0

IF @UserName IS NOT NULL OR

@LastName IS NOT NULL OR

@FirstName IS NOT NULL

BEGIN

INSERT INTO Individuals(

UserName,

Password,

FamilyID,

LastName,

LastNameView,

FirstName,

FirstNameView,

MiddleName,

MiddleNameView,

DOB,

DOD,

BirthLocationID,

Gender,

BirthInfoView,

Address,

AddressLocationID,

ZipCode,

RelationshipID,

BloodRelated,

PersonalInfoView,

PictureURL,

WebsiteURL,

Tested,

TestedDate,

DiagnosisTypeID,

DiseaseID,

DiagnosedDate,

TransplantDate,

TransplantHospital,

TransplantLocationID,

ScienceInfoView,

DateCreated,

DateLastModified,

FamilyOwner,

CanChangeFamilyInfo,

LastIP,

Active)

VALUES(

@UserName,

@Password,

@FamilyID,

@LastName,

@LastNameView,

@FirstName,

@FirstNameView,

@MiddleName,

@MiddleNameView,

@DOB,

@DOD,

@BirthLocationID,

@Gender,

@BirthInfoView,

@Address,

@AddressLocationID,

@ZipCode,

@RelationshipID,

@BloodRelated,

@PersonalInfoView,

@PictureURL,

@WebsiteURL,

@Tested,

@TestedDate,

@DiagnosisTypeID,

@DiseaseID,

@DiagnosedDate,

@TransplantDate,

@TransplantHospital,

@TransplantLocationID,

@ScienceInfoView,

getDate(),

getDate(),

@FamilyOwner,

@CanChangeFamilyInfo,

@LastIP,

@Active)

END

IF @@ERROR = 0

SET @ID = @@IDENTITY

RETURN @@RowCount

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

David Webb-CDS

SSCoach

Points: 17398

June 23, 2008 at 10:28 am

#832879

... and you're sure you're not calling the proc twice? Is there a trigger on the table?

And then again, I might be wrong ...
David Webb

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

One thing that might be throwing you off is that @@rowcount returns the rowcount of the LATEST OPERATION, in this case the IF statement., so your return isn't telling you anything about what happened during the insert.

That being said - are you looking at return messages or in the table to determine how many rows are being inserted? The syntax as it stands would only insert one row per execution cycle (unless there's something else like some kind of trigger in play).

Are you sure this is getting called just once?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

gregory.sears SSC Rookie Points: 36 More actions · Answer 3

This is new development so I have the luxery of completely deleting the table data and running my code from scratch. After I clear the database table I run the script exactly one time and I am getting two rows. Also, because I am the only developer, I do know that there are no triggers because I haven't written any yet.

Thanks

noeld SSC Guru Points: 96590 More actions · Answer 4

noeld

SSC Guru

Points: 96590

June 23, 2008 at 11:32 am

#832936

@@ROWCOUNT, @@IDENTITY and @@ERROR should be all read at ONCE.

IF you have two rows and no triggers is definitely because you call it twice 😉

* Noel

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Try this - run profiler, and then run your code. Double-check what is happening.

There's definitely something odd going on, since the VALUES syntax only outputs one row. Like noeld mentioned - without a trigger in play, the only other possibility is the INSERT call happening twice.

If you run profiler, you should actually see several calls of the INSERT.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

gregory.sears SSC Rookie Points: 36 More actions · Answer 6

I was afraid of a response like that. The SQL Server I'm running is on a hosting companies computer. This is a Web App where I do not have direct access to run profiler. That is why I have had to simply run basic code across my procedure to verify and identify where the problem was occurring. After great lengths I have identified it is in the procedure by literally placing code on either side of the execute command of the procedure and stopping and checking the results. If there is a way of checking using sql comands, that is my only option.

Thanks