In order to pull in data from different sources and aggregate it at the end and to remove duplicates I dumped all the records I was accumulating into a temp table. The problem I had was getting the information from that temple table to the other stored procedure that was creating the other reports.
At first I was using a local temp table using the #TableName syntax. This was only available to the current stored procedure, so I had to change that to a global temp table using the ##TableName syntax. After that the second stored procedure could use the temp table information. Unfortunately, since it was global, even one who ran it was using the same information and were stepping all over each other.
I next decided that I would not drop the table at the top of the first store procedure, but create the records storing SPID in field in the temp table, so that I would only see my records and not some else's. As I thought about it more I thought it might be best to use the UserId of the currently logged in user. This would allow me to delete all of my records from previous runs without disturbing others' records.
From what I have seen the global temp table drops itself after some period of inactivity, so that is good too, but as a precaution I am considering also storing a datetime field on the records I am creating as well as the user name, so that I can delete records older that a certain number of minutes, since the report is run and looked at and the temp data is no longer needed.
So that I can dynamically create the temple table from a query I put in code to check for the temp table existing.
IF OBJECT_ID('tempdb..##NewSales') IS NOT NULL
If it does not exist then I do
INSERT INTO tempdb..##NewSales
SELECT {FieldList}
If it does exist then I do
SELECT {FieldList} INTO tempdb..##NewSales
No comments:
Post a Comment