SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Records


Stored Procedure Records

Author
Message
bpowers
bpowers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 960
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58817 Visits: 17934
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 Modens 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)
bpowers
bpowers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 960
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58817 Visits: 17934
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 Modens 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)
bpowers
bpowers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 960
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()


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89586 Visits: 38932
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.

Cool
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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58817 Visits: 17934
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search