x

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
  1. 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
  1. 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.
  2. Multiply the QTY by Unit Price for each part.
  3. 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

  4. If the total is greater than the amount in the other table, Send an email to PLMemail

more ▼

asked Aug 09, 2012 at 12:03 AM in Default

technette gravatar image

technette
1.2k 87 102 109

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Aug 09, 2012 at 08:58 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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.
Aug 09, 2012 at 10:02 AM Usman Butt
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.
Aug 09, 2012 at 10:05 AM Fatherjack ♦♦
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?
Aug 09, 2012 at 07:00 PM technette

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.
Aug 09, 2012 at 07:13 PM Fatherjack ♦♦

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.

Aug 09, 2012 at 07:52 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x11

asked: Aug 09, 2012 at 12:03 AM

Seen: 971 times

Last Updated: Aug 10, 2012 at 02:33 PM