Hi
Getting error Inavlid Object Name CTE
ALTER PROC [dbo].[USP_SessionBookPlanningRecommendation] --'0','21,24,272,1415' -- --'0','28,1328' ( --@Rpl varchar(10), @BookID VARCHAR(max), @StudentID VARCHAR(max) ) AS BEGIN declare @cols as nvarchar(max)=''; declare @query as nvarchar(max)=''; with CTE as ( SELECT SP.[ID] ,SP.[BookID] ,B.[BookTitle] ,B.[AmazonURL] ,B.[NumberOfPages] ,B.[Lexile] ,B.BookRPL [CoreRPL] ,(select [CombosBookName] from [dbo].[ComboBookMaster] where [CombosBookID] = B.CombosBookID) [ComboBookName] ,CASE WHEN B.[BookAvailableForSession] = 0 THEN 'N' ELSE '' END AS [Negative List] ,(Select TOP 1 V.[CurrentStatus] FROM [View_SessionDetails] V WHERE V.ID=B.ID) [SessionPlanStatus] ,SP.[StudentID] ,ST.[Name] ,ST.[Name]+' ('+ST.[MobileNo]+')' [StudentName] ,SP.[Status] ,SP.[LastModifiedDate] ,'H' AS [EntryType] ,(select closingstock from [View_WareHouseStockSummary] where BookId = SP.[BookID]) as [ClosingStock] FROM [SessionBookPlanningHistoricalData] SP JOIN [BookDetails] B ON SP.BookID=B.[ID] JOIN [StudentDetails] ST ON SP.[StudentID]=ST.[LoginCode] WHERE SP.[Deleted]=0 Union All SELECT T0.[ID] ,T0.[BookID] ,T0.[BookTitle] ,(Select T1.[AmazonURL] from [BookDetails] T1 where T1.ID = T0.BookID) [AmazonURL] ,(Select T1.[NumberOfPages] from [BookDetails] T1 where T1.ID = T0.BookID) [NumberOfPages] ,(Select T1.[Lexile] from [BookDetails] T1 where T1.ID = T0.BookID) [Lexile] ,T0.[CoreRPL] as [CoreRPL] ,(select T1.[CombosBookName] from [dbo].[ComboBookMaster] T1 where T1.[CombosBookID] = (select T2.CombosBookID from [BookDetails] T2 where T2.Id = T0.BookID)) [ComboBookName] ,T0.[NegativeList] AS [Negative List] ,T0.[SessionPlanStatus] AS [SessionPlanStatus] ,T0.[StudentID] ,T0.[Name] ,T0.[StudentName] ,T0.[Status] ,T0.[LastModifiedDate] ,T0.[EntryType] ,T0.[ClosingStock] FROM [SessionBookPlanningDetailed] T0 WHERE T0.[HistoricalDataCount]=0 ) select * from CTE select @cols = @cols + QUOTENAME(StudentName) + ',' from (Select distinct StudentName from CTE where StudentID in (select [value] from string_split(@StudentID,','))) as tmp select @cols = substring(@cols,0,len(@cols)) set @query = 'select [BookId] [Book ID],BookTitle [Book],[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],[ClosingStock], ' + @cols + ' from (select [BookID] ,BookTitle,[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],Status,[ClosingStock],StudentName from CTE where (('''+@BookID+'''=''0'' OR [BookId] IN('+ cast(@BookID as varchar(Max))+')))) x pivot ( Max(Status) for StudentName in (' + @cols + ')) piv order by BookTitle'; print @query execute (@query) END
Thanks