January 28, 2008 at 9:49 am
I've posted a couple of times recently and have received great answers. Thanks to all. I'm going to the well one more time. 🙂
I have an application where user accounts may be updated. If the updated user is of a particular type then all the users in his group need to be updated accordingly (accomplished in sp). One of the requirements is that an email be sent out regarding each user that is updated but I'm not sure how to pass each user to a stored procedure whose purpose is to insert "pending emails" into a table. The only idea I had was to use a cursor (yuck) and loop through each record? Thanks for the help.
Here is the code that updates the users:
IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)
BEGIN
UPDATE [cdds_User_Profile]
SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE()
WHERE ([AgencyUniqueId] = @Original_AgencyUniqueId
AND [UserId] <> @Original_UserId AND LOWER([AcctStatus]) <> 'retired')
Here are the parameter in the stored procedure that inserts the pending emails:
ALTER PROCEDURE dbo.cdds_Email_Processing
(
@Type varchar(40),
@userid uniqueidentifier,
@AgencyId uniqueidentifier,
@ReplyTxt varchar(50)
)
January 28, 2008 at 10:18 am
Sounds like it would be much simpler to directly insert the pending emails with an INSERT ... SELECT statement and skip the stored proc.
Another option is to create a comma seperated string of the pending email and pass that string to the stored proc. There's lots of code out there to put the ouput of a SELECT in CS string and then parse it on the other end. Search the script archives on this site.
Hope this helps!
JM
January 28, 2008 at 11:52 am
It does seem like a lot of work just to get it to plug into a stored procedure. I was hoping to keep the functionality contained in a central place (i.e., the sp) for future people who have to support this but the more I think about I agree with you on the simplicity. Thanks for the input.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply