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.