asked
While loOp inside the Cursor
I have a cursor and I want to check the value in the variables before I can set the values (managers assistant email), Can I use a while loop inside the cursor, then build the email message using the variables.and a bit of hardcoded text, Can I please get help with the syntax OPEN C_BirthdayReminder (the cursor Name) FETCH NEXT FROM C_BirthdayReminder INTO @ManagerID,@ManagerLoginName,@EmployeeID, @LoginName, @ClientID, @Sex WHILE @@FETCH_STATUS = 0 BEGIN SET @ManagerID = ReminderData.ManagerID SET @ManagerLoginName = ReminderData.ManagerLoginName + '@
domian.co.za;' WHILE @ManagerID = ReminderData.ManagerID (Can I do this?? Check so that I can load the Assistant details) BEGIN IF @EmployeeID != ReminderData.EmployeeID SET @EmployeeID = ReminderData.EmployeeID SET @LoginName = ReminderData.LoginName + '@
domain.co.za;' END IF IF @ClientID != ClientID Begin IF Sex = 3 Begin SET @Gender = 'She' End IF Sex = 2 begin SET @Gender = 'he' End SET @Clientname = ReminderData.Clientname SET @Birthdate = ReminderData,Birthdate SET @Age = ReminderData.Age SET Emailbody = 'It will be the birthday of' + '' + @Clientname + '' + 'on' + '' + @Birthdate + '' + '.' END --IF END --END Inner while Loop(@ManagerID = ClientData.ManagerID ) END -- END Ounter while FETCH NEXT FROM C_BirthdayReminder INTO @ManagerID,@ManagerLoginName,@EmployeeID, @LoginName, @ClientID, @Sex END CLOSE C_BirthdayReminder DEALLOCATE C_BirthdayReminder
I'm having difficulty understanding what you are actually wanting to achieve. I have seen you previous questions, and you seem to be progressing along a certain path with regards sending emails using a cursor. However, I am now not too sure what is going on here, especially with the Reminderdata.[Whatever] data. Are you attempting to use some sort of dataset from a program to fill the cursor? I have also not entirely understood the reason for a cursor inside a cursor. Maybe you could elaborate on what you you want to actually achieve?? You want to send emails to managers reminding them of client's birthdays. These are stored somewhere in the database (a view or function returns them, yes?). Do you want to send one email to each manager with a list of clients and their birthdays, or one email per client per manager?
Hi WillaimD I want to sent a reminder to the Manager and his assistant for Client birthday reminder, I have SP that Retrieve the birthday for Cleints. 1.the ReminderData is Information that is Stored in hash table, in a SP 2.Yes I want to send one email with a list of Clients and their birthdays. the reason for a nested cursor is because, a manager can have more than one assistants.hence I loop using the ManagerID e.g look at Output from ReminderData (In the output below the Manager have Two Assistance ) ClientID | BOD |Sex |EmployeeID |LoginName | ManagerID | ManagerLoginName|ClientsName 5520 | 04/08/1989 | 3 |2255 |FJames | 006 | ACummings |Trevor Minter | Henry 5502 | 04/08/1960 | 2 |5092 |SSmith | 006 | ACummings |Trevor Minter | Jane So I need So Send an Email like this To :
ACummings@domain.co.za,
FJames@domain.co.za,
SSmith@dimain.co.za From: System Generated Subject: Clients birthday It is Henry's birthday on BOD.(Henry retrieved as Client name) [1]: http://
Short answer, no! Long answer,I guess you are still trying to convert your c# code. I would create a procedure that takes a table parameter and creat the cursor of the table parameter ordered by manager Id, loop the cursor to create the strings for each record and send the mail when manager Id changes. To do that you need to store the manager_I'd in a second variable and check when the cursor variable changes.
Why not import all of the client details into a CRM system (Outlook/Exchange if you have no other) and then fix a reminder on each contact for the day or so before their birthday that gets your member of staff to take the necessary action. You can use you dev skills to write the code to create a recurring reminder for each contact that is their birthday date and its all done in a system that is design for this sort of thing. Even automating the email from Exchange would make sense.
