July 11, 2013 at 4:01 pm
Regarding the @query parameter, BOL states:
"Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query."
I'm calling sp_send_dbmail from a code that updates records in a table. I want to send some information about the record that was updated, including data from child tables joined to the updated record.
Since I can't pass a local variable to @query, I'm doing something like:
@query = 'DECLARE @RecordID AS Varchar(12)
SET @RecordID = (select top 1 Record_ID
FROM Table where ColumnChangedFrom1 = 0
ORDER BY Modify_Date DESC);
SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = @RecordID'
While this works so far in testing, I'm not certian that in production it will return the correct records 100 percent of the time. Is there a way to be certain the dbMail query will find the right records?
July 11, 2013 at 6:46 pm
dan-572483 (7/11/2013)
Regarding the @query parameter, BOL states:"Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query."
I'm calling sp_send_dbmail from a code that updates records in a table. I want to send some information about the record that was updated, including data from child tables joined to the updated record.
Since I can't pass a local variable to @query, I'm doing something like:
@query = 'DECLARE @RecordID AS Varchar(12)
SET @RecordID = (select top 1 Record_ID
FROM Table where ColumnChangedFrom1 = 0
ORDER BY Modify_Date DESC);
SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = @RecordID'
While this works so far in testing, I'm not certian that in production it will return the correct records 100 percent of the time. Is there a way to be certain the dbMail query will find the right records?
Why do you even need a variable local to the query?
@query = 'SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = (select top 1 Record_ID
FROM Table where ColumnChangedFrom1 = 0
ORDER BY Modify_Date DESC)'
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 12, 2013 at 9:10 am
I am assuming that the SELECT TOP 1 Record_ID ..... ORDER BY MODIFYDATE DESC is going to return the record that was just modified. Is it safe to assume that if multiple users are doing the same operation at about the same time?
July 12, 2013 at 5:59 pm
I would say that it improves the probability.
Any time you retrieve a record in one SQL statement that you want to be "current" and then use it in a second one, you leave open that infinitesimal instant of time where another query could sneak in and ruin your day.
If you can combine them, the probability is reduced but not rendered to zero. There is a locking hint you can look at: UPDLOCK. I've never used it in a situation exactly like yours. Usually I've used it to lock a record, to use the value on it to update another record in another table. Done within a transaction, the first query holds the lock on the table until the transaction ends.
I'm not sure if it is necessary for your case or not and it would probably depend on exactly how much volume of transactions you're dealing with.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 15, 2013 at 4:55 pm
Just capture value of the RecordID that you just modified in the calling code, and pass it into the code to be dynamically executed:
@query = 'DECLARE @RecordID AS Varchar(12)
SET @RecordID = ''' + CAST(@RecordID_Updated AS varchar(10)) + '''
SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = @RecordID'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply