Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Red face Unanswered: Stored Procedure help again???

    I am completely lost with stored procedures,this is a procedure created by someone else, and I am wondering if somebody could explain to me,how is this suppose to send a simple email???
    Am I suppose to have a table that has all the relevant data so in example here "@recipients=@cRecipients" variable will look at the table and find a recipient, I have no idea??????

    CREATE PROCEDURE SendSimpleEmail
    @cRecipients varchar(255),
    @cSubject varchar(255),
    @cMessage varchar(1024)

    AS


    EXEC master.dbo.xp_sendmail @recipients=@cRecipients,
    @message=@cMessage,
    @Subject=@cSubject,@width=1024,
    @copy_recipients='me@hotmail.com'

    GO

  2. #2
    Join Date
    May 2004
    Location
    Newcastle, Australia
    Posts
    3
    The @ symbols mean you need to pass in a parameter when calling this stored procedure.

    To execute the stored procedure use this code:
    EXEC SendSimpleSmail 'test@hotmail.com','test','test'

  3. #3
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thanks for your reply,I will try it out,but don't feel like I have got an explanantion, you just told me what to do.....
    Regards

    I would like to understand.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I think he just assumed you'd figure it out. When you call this procedure, you have to call it passing in values for the @c??? variables. The @cRecipients can be populated with one or many emails (just like you would populate your To: filed in Outlook. The @cMessage has your email message. The @cSubject has your Subject line.

    This is really all this procedure can do. Now you could modify it to work off of a table by selecting several recipients from a table, making a semicolon-delimited string of them, and populating @recipients with that instead. But, you would have to change the stored procedure to do this. Make sense?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thanks,I think I am beginning to understand...

    CREATE PROCEDURE SendSimpleEmail
    @cRecipients varchar(255),
    @cSubject varchar(255),
    @cMessage varchar(1024)
    AS

    Select recipients,message,subject,copy_recipients from Test_table
    where ID =@ID

    EXEC master.dbo.xp_sendmail @recipients=@cRecipients,
    @message=@cMessage,
    @Subject=@cSubject,@width=1024,
    @copy_recipients='me@hotmail.com'

    GO

    So basically I need a table where all this data is provided,i select the data and then execute the procedure which is basically suppose to send an email,right???
    I will test it!!!!

    One more question?
    If I have a procedure that is checking,doing certain things, and has a select statement collecting certain data that I need to email(all that through a single procedure) how would I create a attachement so when that data gets email,it comes to through as an excel attachement???
    Thanks for your help

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You need to divide it into two stored procedures. One will control the email, the other the message you want to send. If you look in Books Online, there is a paramater to execute a stored procedure. You can just use that option.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •