I would appreciate confirmation on the answer I found digging through T-SQL's documentation.
I have a stored procedure that references a view twice. Once in the procedure and then a second time when executing the dynamic sql statement.
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM CourseSalesView) AS Courses
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Year, ' + @ColumnName + '
FROM CourseSalesView
PIVOT(SUM(Earning)
FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
My database has SQL Server's default isolation level of 'ReadCommited'. If something else changes my view in-between my procedure and sp_executesql will I have an concurrency problem? I.E. Could a row being removed in my view by another user at the exact right time cause a mismatch between this procedure's two statements?
I am under the assumption that the 'ReadCommited' isolation level prevents this from happening and that I do not need to explicitly declare the isolation level anywhere in my Stored Procedure as it is inherited from the default setting.
Best Answer
In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table:
WITH (TABLOCK, HOLDLOCK)
. I just tested this on a View and it does block access until the transaction completes, unless a query uses theWITH (NOLOCK)
hint.Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:
Right before the
EXEC sp_executesql @DynamicPivotQuery
line, add the following two lines:Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the
WAITFOR
finishes it will run the dynamic SQL and you should see the effects of your changes.So the following should work:
Beyond that basic interaction, there are two other options:
Storing the results of the View into a local Temp Table (i.e.
#TableName
). This has the benefit of not locking the underlying table(s) of the View, but it will have to write the entire results of the view intotempdb
and that does not sound like a scalable solution if the View returns 10k rows or more.Enable
SNAPSHOT ISOLATION
(introduced in SQL Server 2005) which handles this for you without needing to explicitly lock the table:READ_COMMITTED_SNAPSHOT
then you will need to issueSET TRANSACTION ISOLATION LEVEL SNAPSHOT
before theBEGIN TRAN