April 15, 2008 at 5:06 am
Hi,
I am writing a stored proc that has take the results in a table and mail to the recipients in the table. There is a column name 'email' and when that gets displayed, it send a mail to the email address within the row.
I am using a cursor to do this but perhaps my logic is not right:
/*declare the cursor*/
declare App_Cur cursor for
SELECT No, email from Vw_APP
open App_Cur
fetch next from App_Cur
while (@@fetch_status = 0)
Begin
FETCH NEXT FROM App_Cur
End
CLOSE App_Cur
DEALLOCATE App_Cur
can anyone perhaps help me with this?
Regards
IC
April 15, 2008 at 5:39 am
[font="Verdana"]Really not getting what do you mean. If possible, psecify with the desired o/p with real life example.
Mahesh[/font]
MH-09-AM-8694
April 15, 2008 at 5:44 am
Pretty close. You'll need to fetch the email into a variable (ie FETCH NEXT ... INTO @SomeVariable), then the sending logic goes inside the while loop just before the FETCH.
Just note that in general, cursors are not a good way of working in SQL. This is probably one of the few exceptions to that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2008 at 5:46 am
Develop a SQLServer stored_procedure that runs everyday.
The Code must email a user if the request count is greater than 0.
April 15, 2008 at 6:15 am
Hi Gail,
Thanks for the info. One more question though, I created the variables that goes into the fetch next.
declare @email varchar(100)
declare @recno int
After this was inserted, I'm only getting back the first record. When the run the view it brings back 4 records.
What am I doing wrong?
Regards
IC
April 15, 2008 at 6:24 am
Dunno. Post the code please?
Make sure you've changed both of the Fetch Next ... to use the variables, otherwie you will only get the first row, 4 times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2008 at 6:34 am
See code below. When executing this below I get no resluts back, juts command executed successfully.
/* declare local variables used for fetch */
declare @email varchar(100)
declare @recno int
Set nocount on
/*declare the cursor*/
declare App_cur cursor for
SELECT recno, email from Vw_App_View
open App_cur
fetch next from App_cur into @recno, @email
while (@@fetch_status = 0)
Begin
FETCH NEXT FROM App_cur into @recno, @email
End
CLOSE App_cur
DEALLOCATE App_cur
April 15, 2008 at 6:40 am
Well, yes. All that's currently doing, is looping over the records. You'll need to add in logic to do whatever inside the while loop.
while (@@fetch_status = 0)
Begin
-- Code to send the mail to the address contained in @email goes here...
FETCH NEXT FROM App_cur into @recno, @email
End
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2008 at 6:52 am
Thanks alot for you help
April 15, 2008 at 1:07 pm
Before you go building a report distribution system, if you are using the enterprise edition of SQL you should look up data-drive subscriptions.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply