Is a temporary table the way to do this?

Question

Is a temporary table the way to do this?

webskater

SSCarpal Tunnel

Points: 4214
More actions
April 6, 2008 at 6:21 am

#184813

Within a stored procedure I need to run a select statement and then loop through the results taking the values from the columns returned and passing them as parameters to a stored procedure.
So, in pseudo code.
Declare @Email varchar(100)
Declare @CustomerName varchar(100)
Select @CustomerName = CustomerName, @Email = Email FROM Customerstbl
I then need to loop through the records passing the data returned to a stored procedure ...
EXEC sp_UpdateMailingList @Email, @CustomerName
I would normally do this by using a cursor, 'populating' it with the select statement into the cursor - and then loop through executing the stored procedure recursively.
Is this the right/approved/best way of doing this? I keep reading that cursors are a bad idea.

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

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Part of the problem here is you are limiting yourself with a stored procedure. Why not directly update the mailing address table? This is assuming that you are updating a table MailingList.

UPDATE MailingList

SET EMAIL = Customer.Email

FROM Customer

INNER JOIN MailingList

ON Customer.customerid = MailingList.customerid

WHERE Customer.Email <> MailingList.email

My blog: http://jahaines.blogspot.com

GSquared SSC Guru Points: 260824 More actions · Answer 2

What does sp_UpdateMailingList do? Quite possibly, the whole thing can be turned into a single command that doesn't require a cursor.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 3

Thanks for your replies. What I am actually doing is gettign a list of people and then looping through that list passing an ID to another prodecure which might return 1 to 4 rows of data per person.

I loop through this and, depending on the values of various fields returned, construct and send emails or text messages to the people in the 'list' for each person.

I do it at the moment using a cursor but I am no SQL expert - just picking things up as I go along - and I wondered if this is the right way to do this sort of thing. (It isn't as simple as the way I phrased the question which I figure I could do with an update statement with a join in it).

Thanks again.

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 4

How important is this for performance? If it's a batch, send a few emails here and there, I might stick with the cursor. Not sure a temp table helps here.

However if it's something that you need to be sure you've processed xx rows and if something happens you need to start from the middle, not the beginning, I might create a real table, populate it,and then process as a while exists (select 1 from mytable where processed is null), to work with each id. I'd also then mark when they were processed.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Are you sending emails using the Database itself or using an Application? If it is an application, You can do one single query that joins to the other table.

You can retrieve this whole record set into your application and then send emails using a loop there.

You can set the texts for the email from the stored proc itself if you use CASE statements. From what I read from your explanation, you can use JOIN and CASE statements to get what you want.

-Roy

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 6

Thanks for your reply.

I'm calling a Com Object to send the emails.