Create Dataset from Stored Procedure Temp Table.

Question

Hi all ,
I try to create a RDLC report in C# WinForm application. The datasource from MSSQL Stored Procedure Temp Table. But when I Create DataSet -- TableAdapter -- select this SP , showing error "Invalid Object name #TempPDes".
Its something wrong in my SP?
Its kind of urgent. Please help.

Stored Procedure:

Create PROCEDURE [dbo].[PBundle] @PD as varchar(10)  
AS  
declare @DO as varchar(10)   
declare @PSFT as real  
declare @DSFT as real  
declare @MTL as varchar(50)   
declare @BundleCount as real  
declare @Bundle as varchar(5)   
  
  
create table #TempPDes  
(  
DO varchar(10),  
PD varchar(10),  
 PSFT real,  
 DSFT real,  
 MTL varchar(50),   
 BundleCount real,  
 Bundle varchar(5)  
)  
  
declare PD_list cursor scroll for select [Do],[ACT],[BundleNo],[DSFT] FROM [V - Detail All] WHERE  (PD = @PD)  
open PD_list   
fetch next from PD_list into @DO,@bundle,@DSFT  
while @@fetch_status=0  
begin  
  
set @MTL=(select MTL_NO from [dbo].[V - Description MTL] where Do=@DO )  
set @PSFT=(select top(1) SFT from [dbo].[T - PD] where PD=@PD )  
Set @BundleCount=(SELECT  COUNT(BundleNo) AS BNC FROM [V - Detail All] GROUP BY Do, PD HAVING(PD = @PD) and Do=@DO)  
  
  
insert into #TempPDes (DO,PD,PSFT,DSFT,MTL,BundleCount,Bundle)values(@DO,@PD,@PSFT,@DSFT,@MTL,@BundleCount,@Bundle)  
  
fetch next from PD_list into @DO,@bundle,@DSFT  
end  
  
close PD_list  
  
deallocate PD_list  
  
select * from #TempPDes  



  

Answer 1

Hi @Kerry Ou

So, if I don't input values of parameter. How Can I create a new dataSet , and the datasource from my SP #TempPDes ?

First, your SP has a variable @PD and it must have a value when you excute your SP.
You could modify the first line of this code to this Create PROCEDURE [dbo].[PBundle] @PD as varchar(10) ='default_value_you_want' to assign a default value to the variable.

Second, i just found that there is no Drop table #TempPDes in your code, you need to add it at the end of your SP code.
Check this:

 Create PROCEDURE [dbo].[PBundle] @PD as varchar(10) ='default_value'--modify this value  
 AS  
 BEGIN  
 declare @DO as varchar(10)   
 declare @PSFT as real  
 declare @DSFT as real  
 declare @MTL as varchar(50)   
 declare @BundleCount as real  
 declare @Bundle as varchar(5)     
      
 create table #TempPDes  
 (  
  DO varchar(10),  
  PD varchar(10),  
  PSFT real,  
  DSFT real,  
  MTL varchar(50),   
  BundleCount real,  
  Bundle varchar(5)  
 )  
      
 declare PD_list cursor scroll for select [Do],[ACT],[BundleNo],[DSFT] FROM [V - Detail All] WHERE  (PD = @PD)  
 open PD_list   
 fetch next from PD_list into @DO,@bundle,@DSFT  
 while @@fetch_status=0  
 begin  
      
 set @MTL=(select MTL_NO from [dbo].[V - Description MTL] where Do=@DO )  
 set @PSFT=(select top(1) SFT from [dbo].[T - PD] where PD=@PD )  
 Set @BundleCount=(SELECT  COUNT(BundleNo) AS BNC FROM [V - Detail All] GROUP BY Do, PD HAVING(PD = @PD) and Do=@DO)   
      
 insert into #TempPDes (DO,PD,PSFT,DSFT,MTL,BundleCount,Bundle)values(@DO,@PD,@PSFT,@DSFT,@MTL,@BundleCount,@Bundle)  
      
 fetch next from PD_list into @DO,@bundle,@DSFT  
 end    
 close PD_list  
 deallocate PD_list  
      
 select * from #TempPDes  
 drop table #TempPDes  
END  

Answer 2

Hi @Kerry Ou
Did you execute this SP before you select * from #TempPDes?

Best regards,
LiHong

Answer 3

There is nothing wrong with your stored proc. Your problem is in your C# code. Please post your code.