Stored Procedures???

Stored Procedures???

Post by Rob » Thu, 01 Nov 2001 05:58:02



Hello, I'm fairly new to SQL, Enterprise Manager and SQL
Server and have
never used Stored Procedures before.

I'm trying to create a procedure, that every Wednesday, at
midnight that
sends a dynamic email (xp_sendmail??).

The email should send to each address, all the deal names,
descriptions and
experation dates.

I have created two procedures with the select statements
to get the info I need:
CREATE PROCEDURE [owner].[sp_getemails] AS
SELECT mem_email_un, mem_first
FROM members
WHERE mem_newsletter = 1 AND mem_fakeemail = 0
GO

CREATE PROCEDURE [owner].[sp_getexpired] AS
SELECT deal_name, deal_descript,deal_status_exp, deal_id,
deal_url
FROM deals
WHERE DateDiff(day, GETDATE(), deal_status_exp) BETWEEN 0
and 7
GO

I'm not sure where to go from here. Or even if that was
the right place to start. I think I need to use
xp_sendmail, but
haven't found the right documentation on how to use it and
put everything together.

Could someone offer some suggestions and/or point me in
the right direction.

Thanks for your time!
ROB

 
 
 

Stored Procedures???

Post by James Hunter Ros » Thu, 01 Nov 2001 07:31:19


With greatest respect, I think you might need to get some help on this task.
COnfiguring SQL Mail, although not horribly difficult, can be tricky, and
certainly requires a good understanding of SQL Server, the SQL Agent, NT
Accounts, etc.  Writing a stored procedure to send email content (possibly
derived from table content) to a list of email addresses (possibly derived
from table content) is not really tough for an experienced TSQL developer.
But, for it to be robust and reliable will liekly require even more
sophistication.

Your sample "stored procedures" are basically just queries, and it's quite a
long way from a query to a procedure.

The SQL Agent can be used to schedule things to run at specified times, so
it would be used to execute your stored procedures once they were written.

In the spirit of helping, I could suggest getting a good book, something
like a "TSQL in 21 Days" type of book.  To learn more about SQL Mail, the
BOL is not a bad place to start.

James

 
 
 

1. Calling a Java Stored Procedure from another Java Stored Stored Procedure

Hi,
I'm using the stored procedure builder of DB2 UDB v6.1 on NT to create a
Java Stored Procedure to call another Java Stored Procedure. Both of
them belong to the same project in stored procedure builder.
The sp that calls another sp has the code as follows:
// Calling another java sp -- ErrorHandler
ErrorHandler err = new ErrorHandler();
err.execute("Test #1", 100, "Testing #1");
When I want to build the sp which calls another sp from within, it gave
me an error as follows:
C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953747549650\
com\intertrac\datamart\sp\SelectBillShipAddr.java:18: Class
com.intertrac.datamart.sp.ErrorHandler not found in type declaration.
ErrorHandler err = new ErrorHandler();
Does anyone have any clue of how this can be properly done ? Please let
me know.
Thanks,
ra

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Scaling of decimal error

3. Stored Procedure calling Stored Procedure

4. Stored procedures, CommandText and unwanted Parameters.Refresh

5. Executing a Stored Procedure in a Stored Procedure and selecting on the result

6. To those TStream gurus out there

7. Call a stored procedure from another stored procedure

8. How do I post getdate() function in text box (asp.net)

9. Calling a stored procedure with parameters from another stored procedure

10. using SQL stored procedure results in stored procedure

11. Using Resultset in Stored Procedure in another stored procedure

12. Stored Procedure using another Stored Procedure

13. Calling a Stored Procedure from a Stored Procedure