You need to pass the
@GID
parameter to the dynamic
PIVOT
query. Use
sp_executesql[
^] to pass the parameter to the dynamic query:
DECLARE @pivot_query NVARCHAR(MAX)
SET @pivot_query = N'WITH CTE AS
(
SELECT A.GROUP_MOD_ID, B.FUNCTION_MOD_NAME, CAST(ALLOW AS INT) AS ALLOW
FROM FUNCTION_GROUP A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID = B.FUNCTION_MOD_ID
WHERE A.GROUP_MOD_ID = @GID
)
SELECT GROUP_MOD_ID, ' + @pivot_cols + N'
FROM CTE
PIVOT (MAX(ALLOW) FOR FUNCTION_MOD_NAME IN (' + @pivot_cols + N')) AS PIV';
PRINT @pivot_query
EXEC dbo.sp_executesql @pivot_query, N'@GID char(3)', @GID = @GID;