I create a procedure:
CREATE PROCEDURE GetPTBV
AS
select *
from pricetobook
declare @sql nvarchar(max);
declare @fields nvarchar(max);
set @fields=stuff((select ',['+Dateformat1+']'
from pricetobook
where Dateformat1 not in ('Code','Mnemonic')
group by Dateformat1
order by case when Dateformat1='Name' then 0 else 3 end
,Dateformat1
for xml path('')
,type
).value('.','nvarchar(max)')
,1
,1
,''
)
;
print @fields
set @sql='
select '+@fields+'
from (select Dateformat1
,Dataformat2
,sum(rn1*rn2) over(order by rn2) as rn
from (select Dateformat1
,Dataformat2
,case when Dateformat1=''Name'' then 1 else 0 end as rn1
,row_number() over(order by (select null)) as rn2
from pricetobook
) as a
) as a
pivot (max(Dataformat2) for Dateformat1 in ('+@fields+')) as p
'
;
execute sp_executesql @sql;
GO
Then:
SELECT * INTO #TestTabelPTBV FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.GetPTBV')
-- Select Table
SELECT *
FROM #TestTabelPTBV;
**But it keeps me replying: **
Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'tempdb.dbo.GetPTBV'.
Could someone help me.. please
R.H.