Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure Records Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 10:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:06 PM
Points: 288, Visits: 815
I have created a stored procedure that will return approved purchase orders when executed. However, if I have it kickoff several times a day I do not want to return purchase order records that were returned on the previous executions. Any ideas on the best way to set this up? I thought about putting the data over into a temp table to do the comparisons or maybe have it look at time stamp info. This will actually be kicked off from a SharePoint workflow. Any help or suggestions will be greatly appreciated.
Post #1437993
Posted Tuesday, April 2, 2013 10:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
Maybe just add a DateSent column to your table? Then you can update that when you send the notification (good for historical purposes) and then your main logic only gets those that have DateSent is null. There are number of ways to do this type of thing. A lot of the decision comes down to what you need the process to do etc etc etc...

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1437998
Posted Tuesday, April 2, 2013 10:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:06 PM
Points: 288, Visits: 815
Thanks for the reply. I was hoping to accomplish this using some dynamic code, rather than maintain a static table, but that may be my best option. Thanks again.
Post #1438006
Posted Tuesday, April 2, 2013 12:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
bpowers (4/2/2013)
Thanks for the reply. I was hoping to accomplish this using some dynamic code, rather than maintain a static table, but that may be my best option. Thanks again.


Yeah if you want to know if something was sent in the past you have to store it somewhere.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438055
Posted Wednesday, April 3, 2013 9:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:06 PM
Points: 288, Visits: 815
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()

Post #1438423
Posted Wednesday, April 3, 2013 9:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 20,798, Visits: 32,714
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()



Would need to know the structure of the table and the data stored, but I would see a select on the table returning a single value to be used in the WHERE clause.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438445
Posted Wednesday, April 3, 2013 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,205, Visits: 12,687
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse