This post has been wikified, any user with enough reputation can edit it.
asked
Stored Procedure executing multiple statements
I'm working on the following stored procedure but may need a little help.. Any help is appreciated. My stored procedure is executed from a trigger and receives a parameter (QuoteID) In my stored procedure 1. I must select the following data to prepare for an email that will be sent SELECT QL.QuoteID, QL.PartID, QL.PartDesc, QL.OnticPartID, QL.CustPartID, QL.Qty, QL.LicenseCode, QL.TotalPrice, QL.UnitPrice, T.QuoteDate, T.Buyer, T.SalesRep, T.PlmName, T.PlmEmail FROM QLINES QL INNER JOIN QRT T ON QLINES.QuoteID = QRT.QuoteID Where QL.QuoteID = @QuoteID 2. Select the following data from another database based on the Part Numbers in the first set of data in the previous statement. FOR EACH PART do the following SELECT TOP 1
Q.ID AS QUOTE_ID ,Q.QUOTE_DATE AS QUOTE_DATE ,QL.LINE_NO AS QUOTE_LINE_NO ,QP.QTY AS QUOTE_QTY ,QP.UNIT_PRICE AS QUOTE_PRICE FROM QLINE QL LEFT JOIN QUOTE_PRICE QP ON QL.QUOTE_ID = QP.QUOTE_ID AND QL.LINE_NO = QP.QUOTE_LINE_NO LEFT JOIN QUOTE Q ON QL.QUOTE_ID =
Q.ID WHERE QL.PART_ID = @ AND LEFT(QL.QUOTE_ID,1) = 'E' " ORDER BY Q.QUOTE_DATE DESC 3. I will need to put the first of each PART, **FIRST SET QTY**, **FIRST SET LicenseCode** , **FIRST SET PLMname**, **FIRST SET PLMemail**, UNIT_Price in a temp table. 4. Multiply the QTY by Unit Price for each part. 5. In the last step, I will evaluate if the total of each part is greater than or equal to an amount in another table where LicenseCode=LicenseCode and PLMname = PLMname 6. If the total is greater than the amount in the other table, Send an email to PLMemail
This scenario is screaming out for a solution that involves Service Broker and not a trigger. You could see a whole lot of trouble putting logic that spans databases in a trigger. Essentially the session that causes the trigger to fire will be waiting while the code in the trigger executes. The transaction will be open and causing locks and blocks while the code you show executes. If this execution gets held up for any reason then other transactions like your first one will be blocked. Your whole system could come to a standstill very quickly and very frequently. Service Broker allows for two disconnected systems to play their part in a process without affecting the execution of the other in any detrimental way. Triggers are a good way to apply business logic/rules/conditions to data but should be used carefully as they also have the ability to cause a system a lot of pain. I know this isnt going to be an answer you were hoping for but if you are early enough in the design of this system that you can build it with Service Broker then you will be building a system that will be robust and will scale a lot better as the system(s) grow.
I totally agree with that. Great advice. But if you cannot use Service Broker, then schedule a job that will regularly check the criteria and send mails accordingly.
For sure, if you are Ok to run it nightly then use the trigger to make a table of work to be actioned over night then use a SQL Agent job to apply as much logic or as many steps as you want/need to take.
Yeah, a manual version of the SB architecture where it is acceptable to have the process run on a cycle like that is also a good solution.
I have just read about 10 articles on service broker. It seams like this is structured for database to database communication. I'm just trying to retrieve data from tables, calculate and evaluate the results, then send an email. Does anyone have an example of this through service broker?
Can you give us any idea about the acceptable duration between the first transaction taking place and the email being sent please? The main "red-flag" in your question is the embedding of this logic inside a trigger. This exposes the transaction to a much greater risk of locking/blocking. If you can accept a little delay then you can have your first transaction just write a row into a logging table. This will be as little delay in your trigger as possible and let the primary transaction continue unhindered. Then a SQL Agent job can cycle (every minute/5 minutes/10 minutes - it depends on your requirements) through this table and carry out the second step of your logic. This disconnects the cross database work from the primary system work and means they will be less likely to interfere with each other.
9 People are following this question.
