Need help with a query and while loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with a query and while loop

Author

Topic

jasonwills
Starting Member

3 Posts

Posted - 2011-03-13 : 16:46:54

Hi All,

i'm struggling with a query and could do with some help.

i have a table with a date column.

what i'd like to do is to return all rows with the date is within 30 days from now.

once i have this i'd like to loop through each returned row and email it using send_db_mail, and then change another column called Emailed to 1 rather than the default of 0.

hope someone can help.

Thanks in advance.

Jason

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-13 : 22:47:22

>> I have a table with a date column. <<

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. You did not even tell us the name of this table!

>> what I’d like to do is to return all rows with the date is within 30 days from now. <<

SELECT <<list of columns we never saw >>
FROM <<nameless table >>
WHERE vague_date BETWEEN CAST (CURRENT_TIMESTAMP AS DATE)
AND DATEADD(DAY, 30, CAST (CURRENT_TIMESTAMP AS DATE))

>> once I have this I’d like to loop through each returned row and email it using send_db_mail, and then change another column called Emailed to 1 rather than the default of 0. <<

Good SQL programmers do not use assembly language flags; we would have the mailing date. We also do not like loops since this is a set-oriented language. The recipient list option is the best you can do with this procedure, so use it.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jasonwills
Starting Member

3 Posts

Posted - 2011-03-14 : 16:26:12

well jcelko,

thankyou for your advice - i'm sure in the section called "New to SQL Server Programming" your bluntness will encourage a lot of people.

Regards

Jason

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources