SQLTeam.com | Weblogs | Forums

How to INSERT data from Stored Procedure to Table

tsql
sql2014

#1

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.