use TipData > >declare @SQL varchar(max), @Columns VARCHAR(max), @AmendmentNumber varchar(7) >set @AmendmentNumber = '2014.01' > >set @columns = stuff(( >select ', ' + quotename(FundType) >FROM ( >SELECT FundType from TIP_AmendmentProposedFunding P >WHERE AmendmentNumber = @AmendmentNumber >UNION >SELECT FundType FROM TIP_AmendmentApprovedFunding >WHERE AmendmentNumber = @AmendmentNumber >) S >ORDER BY FundType >FOR XML PATH('')), 1,2,'') >--FOR XML PATH(''),type).value('.', 'varchar(30)'), 1,2,'') >-- type and value were used in case the FundType contained some characters that may choke XML > > >-- list of new columns > >set @SQL = >';with cte as ( >select coalesce(p.amendmentnumber, a.amendmentnumber) as AmendmentNumber, >d.ItemNumber, >coalesce(p.tipid, a.tipid) as TipID, >coalesce(p.fundtype, a.fundtype) as FundType, >coalesce(p.AMOUNT,0) - coalesce(A.Amount,0) as NET >FROM (SELECT AmendmentNumber, FundType, TipId, SUM(Amount) as Amount >FROM TIP_AmendmentProposedFunding WHERE AmendmentNumber = @AmendmentNumber >GROUP BY AmendmentNumber, FundType, TipId ) P >full outer join (SELECT AmendmentNumber, FundType, TipId, SUM(Amount) as Amount >FROM TIP_AmendmentApprovedFunding WHERE AmendmentNumber = @AmendmentNumber >GROUP BY AmendmentNumber, FundType, TipID) A >on P.TipID = a.TipID and p.AmendmentNumber = a.AmendmentNumber and p.FundType = a.fundtype >full join TIP_AmendmentDetails d >ON d.AmendmentNumber = p.amendmentnumber and d.TipID = p.tipid > -- do you need that number only - if yes, add this also into derived tables >) > >SELECT * FROM cte PIVOT (sum(NET) FOR FundType IN (' + @Columns + ')) pvt' > >--print @Columns; -- examine the list of generated fund types >PRINT @SQL; -- examine the SQL > >execute sp_ExecuteSQL @SQL, N'@AmendmentNumber varchar(7)', @AmendmentNumber ;>produces this error.
>Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 >Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.So, can you read and understand what does this message tell you?