Why does the insert into tbl_Campaign_List happen twice for each time through the loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Why does the insert into tbl_Campaign_List happen twice for each time through the loop

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-12 : 08:11:41

Brian Pregler writes "CREATE PROCEDURE sp_Respondant_Insert

@CampaignID int

As
Declare
@ExistingRespondantID integer,
@First_Name nvarchar(150),
@Last_Name nvarchar(150),
@Address nvarchar(250),
@City nvarchar(150),
@State nvarchar(50),
@Zip nvarchar(50),
@Phone nvarchar(50),
@Email nvarchar(250),
@Graduation_Year nvarchar(50),
@GPA nvarchar(50),
@Major nvarchar(150),
@Extracurricular nvarchar(150),
@Pref_Environment nvarchar(50),
@Religious_Pref nvarchar(50),
@Pref_Category nvarchar(50),
@Ethnicity nvarchar(50),
@Gender nvarchar(50),
@Change nvarchar(250),
@Updated nvarchar(5),
@Count integer

Set @Count = 1
BEGIN TRANSACTION Inserted
WHILE (SELECT COUNT(Updated) as 'Number of Rows' FROM tbl_TempRespondant) >= @Count
BEGIN

SELECT @First_Name = First_Name, @Last_Name = Last_Name, @Address = Address,
@City = City, @State = State, @Zip = Zip, @Phone = Phone, @Email = Email,
@Graduation_Year = Graduation_year, @GPA = GPA, @Major = Major,
@Extracurricular = Extracurricular, @Pref_Environment = Pref_environment,
@Religious_Pref = Religious_pref, @Pref_Category = Pref_category,
@Ethnicity = Ethnicity, @Gender = Gender, @Updated = Updated
FROM tbl_TempRespondant
WHERE RowID = @Count and Updated = 'No'

IF EXISTS

(SELECT First_Name, Last_Name, Address,
City, State, Zip, Phone, Email,
Graduation_year, GPA, Major,
Extracurricular, Pref_environment,
Religious_pref, Pref_category,
Ethnicity, Gender, Updated
FROM tbl_TempRespondant
WHERE RowID = @Count and Updated = 'No')

INSERT INTO tbl_Respondant
(First_Name, Last_Name, Address, City, State, Zip,
Phone, Email, Graduation_Year, GPA, Major, Extracurricular,
Pref_environment, Religious_pref, Pref_category, Ethnicity, Gender)
VALUES
(@First_Name, @Last_Name, @Address, @City, @State, @Zip,
@Phone, @Email, @Graduation_Year, @GPA, @Major, @Extracurricular,
@Pref_Environment, @Religious_Pref, @Pref_Category, @Ethnicity, @Gender)

SELECT @ExistingRespondantID = RespondantID FROM tbl_Respondant Where First_Name = @First_Name and
Last_Name = @Last_Name and Phone = @Phone and Graduation_year = @Graduation_year

INSERT INTO tbl_Campaign_List
(CampaignID, RespondantID)
VALUES
(@CampaignID, @ExistingRespondantID)

UPDATE tbl_TempRespondant
SET Updated = 'Yes'
WHERE RowID = @Count
--Error Handling
If @@ERROR <> 0 GOTO E_General_Error

SET @Count = @Count + 1
END
COMMIT TRANSACTION Inserted
TRUNCATE TABLE tbl_TempRespondant
RETURN

E_General_Error:
Print 'ERROR'

ROLLBACK TRANSACTION Inserted
TRUNCATE TABLE tbl_TempRespondant
RETURN"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-12 : 08:45:26

dude, time to learn some set-based SQL:


insert into tbl_Respondant (col1, col2, ... )
select col1, col2, ...
from tbl_TempRespondant
where Updated = 'No'

truncate table tbl_TempRespondant

that does everything your stored proc did in two statements and will be about 1,000 times faster (literally !). No variables. No loops.

- Jeff

bprego
Starting Member

13 Posts

Posted - 2003-09-12 : 09:21:42

your solution does not address the entire question. before you put down someones code read it all not just a quarter of it.

bprego
Starting Member

13 Posts

Posted - 2003-09-12 : 09:24:37

my question is, why does the insert into tbl_Campaign_List happen twice for each time through the loop

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-12 : 09:35:23

I think Jeff was trying to suggest that you don't need to loop in this way.

You can do the whole thing in two SQL statements. He's written the first one for you. All you need in addition is one that inserts campaign and respondant ids from tbl_respondant that are not already present.

Then your issue of the why it does it twice is irrelevant.

-------
Moo. :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-12 : 09:42:05

Ooops, yes there is one more step, I missed that one. As Mr Mist says, it can be done with 1 more small INSERT statement.

my question is: why do you have a loop in your stored procedure?

Write out what your stored procedure is trying to do, in plain but detailed english, step by step.

Is the issue you don't know the RespondantID until after the insert because it is an identity, so that is why you are doing 1 at a time in a loop?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-12 : 09:44:48

quote:

your solution does not address the entire question. before you put down someones code read it all not just a quarter of it.

Hey guess what. I was trying to help you. I don't get paid for this. I usually don't get a thanks.

Good luck. But be careful because the #1 thing prohibiting many people from learning SQL efficiently and how to think in a set-based manner is ignorance: thus, I suspect you might have some difficulty.

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-12 : 09:47:17

Where and What is table tbl_Campaign_List, does it reside in master?

Voted best SQL forum nickname...."Tutorial-D"

bprego
Starting Member

13 Posts

Posted - 2003-09-12 : 11:39:09

jsmith8858 is right on I dont know the respondant ID until after the insert. Here is what i do.

I insert a list into tbl_TempRespondant, then I run an update SP that is not posted here which updates tbl_Respondant. If an update takes place I mark in tbl_TempRespondant by make Updated = 'Yes'

then I insert the rest of the information into tbl_Respondant as long as Updated = 'No'

The problem is that after I've inserting each record one at a time into tbl_Respondant I need to insert the NEWLY created RespondantID and the passed CampaignID into tbl_Campaign_List. i know I'm new at this and am sorry if I offended anyone. Thanks for all your help.

bprego
Starting Member

13 Posts

Posted - 2003-09-12 : 11:41:56

to sitka tbl_campaign_list is in my data base and it stores the campaignID and RespondantID, this way I know where each respondant came from.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-12 : 12:02:38

quote:
Originally posted by jsmith8858

dude, time to learn some set-based SQL:

Easy easy on the guy man... we're not as brainy as you Mr Smith.

__________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources