bpowers (4/3/2013)
I built a staging table and I am writing the last execution time of the sp to that table. How do I now use the execution time in the WHERE caluse of my sp, when the staging table has no logical join to the tables in my sp? Any help will be appreciated.
WHERE PURCHASE_ORDER.ADDED_DATE BETWEEN dbo.STOREDPROC.EXECUTED_TIME AND GETDATE()
This is why in my original post I suggested adding a DateSent column to your PO table. You don't really need another table for this, you just need to know what rows have already had a notification sent.
WHERE PURCHASE_ORDER.DateNotificationSent IS NULL
The other way would be to use a subquery.
WHERE PURCHASE_ORDER.ADDED_DATE BETWEEN (Select MAX(EXECUTED_TIME) from HistoryTable) AND GETDATE()
The downside of this is that you don't account for execution errors and some have the ability of getting missed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/