I have two stored procedures. The first dynamically generates a crosstab table (called tbl_Crosstab) based upon a specified date range. The second gets the data out of this new table, formats it and generates the data for XML output. These commands are shown below. The Create SP is used by other areas of the DB so I can't include the display in that SP or use temporary tables. I am worried that I need to lock the newly created tbl_Crosstab until the data is grabed by the Display SP so that any other resource is not grabbing invalid data. How do I do this? Also, if I am creating this new table, are all of the entries being added to the transaction log for the DB? These values do not need to be backed up (since they change constantly and are only used when created) and they could be creating large transaction logs. How do I also disable the logging of the creation of the table and data inserts?
EXECUTE usp_CreateCrossTabTable @StartDate, @EndDate