Loop through query and exec a stored proc for each record 3

[ElJayWilson]

I am trying to figure out how to loop through a recordset and call a stored proc (from within a stored proc) to send out emails. We have an SP that will do all the work of sending out emails, I just need some help in modifying my SP to send one email for every record in the recordset.

Here is my query that I am using in my SP currently:

SELECT dbo.ProperCase(st.FirstName)
  		   + ' ' + 
  		   dbo.ProperCase(st.LastName) as [Name]
  		   , sv.id AS [Visit ID]
  		   , aw.Mneumonic AS Mneumonic
  		   , CONVERT(VARCHAR(10)
  		   , sv.visit_date, 101) AS [Visit Date]
  		   , sv.award_fk AS [Award #]
		FROM dbo.Scheduled_Visits sv
		LEFT JOIN [CHAOS].[dbo].[Awards] aw on aw.AwardNumber = sv.award_fk
											AND aw.Mneumonic IS NOT null
		RIGHT JOIN dbo.Subjects_tbl st on st.SubjectID= sv.subject_fk
		WHERE sv.is_finalized = 0 and sv.visit_date < GETDATE() 
		GROUP BY aw.Mneumonic, 
				sv.award_fk,
				sv.id,
				sv.visit_date,
				st.LastName, 
				st.FirstName 
		ORDER BY Mneumonic

I can use sv.id (Visit ID) to get the email address of the person I need to email.

How would I "loop" through the records to Exec the other SP?

 

[bborissov]

DECLARE @Temp TABLE (Name varchar(200), VisitId int, Mneumonic as varchar(200), VisitDate as varchar(10), AwardNo int)

INSERT INTO @Temp
SELECT dbo.ProperCase(st.FirstName)
             + ' ' +
             dbo.ProperCase(st.LastName) as [Name]
             , sv.id AS [Visit ID]
             , aw.Mneumonic AS Mneumonic
             , CONVERT(VARCHAR(10)
             , sv.visit_date, 101) AS [Visit Date]
             , sv.award_fk AS [Award #]
        FROM dbo.Scheduled_Visits sv
        LEFT JOIN [CHAOS].[dbo].[Awards] aw on aw.AwardNumber = sv.award_fk
                                            AND aw.Mneumonic IS NOT null
        RIGHT JOIN dbo.Subjects_tbl st on st.SubjectID= sv.subject_fk
        WHERE sv.is_finalized = 0 and sv.visit_date < GETDATE()
        GROUP BY aw.Mneumonic,
                sv.award_fk,
                sv.id,
                sv.visit_date,
                st.LastName,
                st.FirstName
        ORDER BY Mneumonic

DECLARE @cId int
SELECT @cId = MIN(VisitId) FROM @Temp
WHILE @cId IS NOT NULL
    BEGIN
         EXEC SomSP @cId
         --- SET with SELECT I hate this
         --- but if there is no records matching
         --- WHERE clause @cId will stay the same if
         --- I use only SELECT
         SET @cId = SELECT MIN(VisitId) FROM @Temp
                           WHERE VisitId > @cId
    END

--- Get your recordset
SELECT * FROM @Temp

Check deeclaration of the @Temp table and adjuct field types so they match the types from the query

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[genomon]

How would I "loop" through the records

I like Boris' solution. But if you are unfamiliar with "looping", have a look at cursors in BOL.

If quizzes are quizzical, what are tests?

 

[hmckillop]

genomon

SQLSister will have a heart attack hearing you mention cursors. Bborrisov's solution is spot on and no further to read anything more. Its the best way for what was requested.

EDayWilson, no harm in reading up on the cursors but, you will see the above works exactly as you requested - feel free to thank Bborissov for the good post

"I'm living so far beyond my income that we may almost be said to be living apart

 

[genomon]

I like Boris' solution.

I even gave him a shiny new star. just pointing out an alternative, crappy as it may be for most instances.
Plus, to incur the wrath of SQLsis is not only unthinkable, but suicidal...

If quizzes are quizzical, what are tests?

 

[gmmastros]

Nope. Sorry. I'm not buying it.

Often times you'll hear, "Cursors are bad". I agree with that. I really do. But, while loops are just as bad. Cursors are bad because they loop over a recordset. Well.... what's a while doing? It's doing the same thing, looping over a recordset.

But... here's the thing... there are more options with cursors, so you can probably tweak them a bit to be more efficient than a while loop. Regardless, you're still looping.

SQL Server (under the covers) is really optimized for set based operations. Looping is incredibly slow (by comparison).

Think of it this way....

The performance of cursors versus while loops is like comparing two kids racing across a school yard. Comparing set based code to cursors/while loops is like a race between a 10 year old kid and a sports car.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

George makes a great point (as usual). A question I should have posed is "Why use SQL Server to send emails at all?".
This can easily and more efficiently be done with a host of frontend tools. MS Access & linked SQL tables come to mind, as does VB (yes George I am still using VB6)


If quizzes are quizzical, what are tests?

 

[ElJayWilson]

Since we have an established SP for sending out emails, it only makes sense to use it. I didn't write that one, but it sure works very nicely.

 

[genomon]

Try benchmarking: what happens when you loop in a frontend app & execute the sp as a passthrough query?

If quizzes are quizzical, what are tests?

 

[RiverGuy]

Try benchmarking: what happens when you loop in a frontend app & execute the sp as a passthrough query?

If you're going to write an app--which is what I would do--forget about the email sproc. You can send an email in two lines of code in .Net for example. If the resultset is small enough, just bring the entire resultset into the app, loop through it in memory, and fire off code in the app to send your emails.

 

[ElJayWilson]

It is going to run once a day and have at most 10 records to process. An application for this small job is not an efficient way to do it. The SP is already running once a day (when the Reporting Services report runs), so if I were to go the application route, I would have to build the app and then install it on one of our virtual machines and then I would have the additional work of monitoring that app to make sure it runs properly.

With the SP, all that work is already done.

 

[genomon]

I hear you, ElJay. If it ain't broke....

I would like to point out for the future that SQL 2005 & later supports execution of CRL modules, meaning you can exec any compiled Common Runtime Language code you like (might come in handy down the road?).

If quizzes are quizzical, what are tests?

 

[RiverGuy]

My last thought:

Sending job and server status emails via SQL Server to administrators of the DB server is a fine use of SQL Server. However, using SQL Server as an application development platform to create email blasting programs to send emails to various recipients (customers, employees, etc) is just an improper use of the software.

Cursors are generally bad. Dynamic SQL is generally bad. So all in all, I don't like the solution for many reasons. Good luck!

 

[sqlfable]

It is going to run once a day and have at most 10 records to process.

This is actually sounding more and more like a windows scheduled task and nothing more calling a simplistic exe that is more stable and using less resources from your database server

An application for this small job is not an efficient way to do it.

It is actually the most efficient way of utilizing your resources available. SQL Server is not made to do these types of things. CLR and even starting and shutting down database mail is a resource hog when it boils down to comparison. How many moving parts to the process flow si the key. If you think SQL Server is simplistic in nature outside data storage and serving data up, then there is much to learn on the database server level. This is why member gmmastros goes into why looping structures in SQL Server and any database server is poor performers. Yes, the contruct is there but truely is not meant to act in this manner. The lines between application server and database server are thin but if you cross them, you dive into an area that becomes uickly hard to maintain and alter. Not to mention eror handling and troubleshooting situations and processes that bloat the technology and usage.

The SP is already running once a day (when the Reporting Services report runs)

Reporting services is a report generation platform. It is not a process orientated platform in which you should hide procedural calls or function calls in it. It's not engineered that way. Not a good idea!

I would have to build the app and then install it on one of our virtual machines and then I would have the additional work of monitoring that app to make sure it runs properly

If designed correctly your applications monitor themselves. Be it in windows services or whatever. Now your VM setup is another story. DB Servers on VM is a bigger story. Not sure why you'd need to install it on all the VM servers. Don't you ahve one data source? Aside from all of that, don't you monitor your database server and make sure it's running. Just because it's in SQL Server doesn't mean it's going to run 100%.

Long run I agree with the lot. This is the place for a higher level language and SQL Server should only serve you the data you need to make the SMTP calls. Undertanding not everyone is a programmer, but this is a common task and thousands of examples out there on any given platform. Even a vbs file would probably out perform a SP and poor loop while sp_dbmail'ing everything out