November 11, 2011 at 6:17 am
Hi, i'm running in a problem i can't figure out...
I have this query that work great and quick
SELECT STEP_ID, ISNULL([1],0) as [Disponibilità e Flessibilità], ISNULL([2],0) as [Impegno], ISNULL([3],0) as [Creatività, Manualità e Abilità], ISNULL([4],0) as [Velocità], ISNULL([5],0) as [Leadership], ISNULL([6],0) as [Puntualità], ISNULL([7],0) as [Umiltà], ISNULL([8],0) as [Reattività di fronte alle difficoltà] FROM (SELECT Rating, RATING_ITEM_ID, STEP_ID
FROM View_Ratings
WHERE AREA_ID = 1 AND VALUATION_ID = 161) p
PIVOT
( MAX (Rating) FOR RATING_ITEM_ID IN ( [1], [2], [3], [4], [5], [6], [7], [8] )) AS pvt
ORDER BY STEP_ID;
But i need a dynamic pivot query then i made a sp that generate exactly this sql based on table values and execute with exec sp_executesql.
I wrote the sp and when i execute it sql server thow the exception
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
But the sp is not recursive and i have no triggers in this db.
The stored procedure only compose dynamically the sql and run it then i can't understand what i miss...
Please help
Thanks
Michele
this is the stored procedure code
ALTER PROCEDURE sp_RatingsStepsTable(
@ValuationId int
)
as begin
DECLARE @fields varchar(max);
DECLARE @values varchar(max);
set @fields= '';
set @values= '';
with cte as (select RATING_ITEM_ID, [Description] from ratingitems where area_id = 1)
select @fields = @fields + 'ISNULL([' + cast(RATING_ITEM_ID as nvarchar(10)) + '],0)' + ' as [' + [Description] + '], ' from cte
set @fields = substring(@fields, 1, len(@fields)-1);
with cte2 as (select RATING_ITEM_ID from ratingitems where area_id = 1)
select @values = @values + ' [' + cast(RATING_ITEM_ID as nvarchar(10)) + '], ' from cte2
set @values = substring(@values, 1, len(@values)-1);
declare @q nvarchar(max);
set @q = N'
SELECT STEP_ID, ' + @fields + ' FROM (SELECT Rating, RATING_ITEM_ID, STEP_ID
FROM View_Ratings
WHERE AREA_ID = 1 AND VALUATION_ID = ' + cast(@ValuationId as nvarchar(10)) + ') p
PIVOT
( MAX (Rating) FOR RATING_ITEM_ID IN ( ' + @values + ' )) AS pvt
ORDER BY STEP_ID;'
exec sp_executesql @q;
END
November 11, 2011 at 6:23 am
You might be have something like this in your query window.
ALTER PROCEDURE P
AS
..
..
..
..
-- Verification
EXEC P
Comment out the last line.
-- Verification
--EXEC P
Alter your SP again & verify the results.
November 11, 2011 at 6:35 am
ahahah thank you... what a stupid error i made...
i thought that create procedure doesn't save after END statement...
thanks
Michele
November 11, 2011 at 6:39 am
Most Welcome 🙂
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply