VS2010 doesn't allow temp table in stored procedure

[bluehangook629]

I have a stored procedure that creates temporary table to populate return table.
It works perfectly fine when I execute it on SQL management studio but when I try to import this sttored procedure, it give me an error Invalid object name '#TOTAL' which #TOTAL is the temp table that is created.
Does VS2010 not allow temporary tables in Stored Procedure?

--DROP TABLE #TOTAL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

CREATE TABLE #TOTAL(
InID nvarchar(10),
InDate datetime,
RA_Type VARCHAR(30),
OrderID VARCHAR(30),
Shipper VARCHAR(50),
Carrier VARCHAR(50),
Tracking VARCHAR(50),
BolQty INT,
Qty INT,
UserName VARCHAR(50),
TimeRecord SmallDateTime
)

DECLARE @InID nvarchar(10);
DECLARE @InDate datetime; 
DECLARE @OrderID varchar(50);
DECLARE @RAID VARCHAR(50);
DECLARE @Shipper VARCHAR(50);
DECLARE @Carrier VARCHAR(50);
DECLARE @Tracking VARCHAR(50);
DECLARE @BOL INT;
DECLARE @UserName VARCHAR(50);
DECLARE @TimeRecord SmallDateTime;

DECLARE @CountOfTblInventoryDetail INT;
DECLARE @CountOfTblWrongRa INT;
DECLARE @SumOfTblInBulk INT ;

DECLARE tblIn_Cursor CURSOR FORWARD_ONLY FOR
(
SELECT tblIn.InID as INID, tblIn.Indate as InDate,tblRA_Type.typecode as 'RA Type', tblIn.OrderID as OrderID,C1.CompanyName as Shipper, C2.CompanyName as Carrier, tblIn.Tracking as Tracking, tblIn.BolQty as BOL, tblUser.Fname + ' ' + tblUser.lname as 'User Name', TimeRecord AS 'Time'
FROM tblIn
INNER JOIN tblCompany C1 on C1.CompanyID = tblIn.ShipperID
INNER JOIN tblCompany C2 on C2.CompanyID = tblIn.CarrierID
INNER JOIN tblUser on tblUser.EmpID = tblIn.EmpID
INNER JOIN tblRA_Type on tblRA_Type.TypeID = tblIn.RAID
)

OPEN tblIn_Cursor;
FETCH NEXT FROM tblIn_Cursor INTO @InID, @InDate,@RAID, @OrderID, @Shipper, @Carrier, @Tracking, @BOL, @UserName, @TimeRecord

WHILE @@FETCH_STATUS = 0 
BEGIN
	SELECT @CountofTblInventoryDetail = ISNULL(Count(ControlID),0) FROM tblInventoryDetail WHERE InID = @InID
	SELECT @CountOfTblWrongRA = ISNULL(Count(InID),0) FROM tblWrongRA WHERE InID = @InID
	SELECT @SumOfTblInBulk = ISNULL(SUM(Qty),0) FROM tblInBulk WHERE InID = @InID
	
	INSERT INTO #TOTAL VALUES (@InID, @InDate, @RAID, @OrderID, @Shipper, @Carrier, @Tracking, @BOL, @CountofTblInventoryDetail + @CountOfTblWrongRA + @SumOfTblInBulk, @UserName, @TimeRecord)
	
	FETCH NEXT FROM tblIn_Cursor INTO @InID, @InDate,@RAID, @OrderID, @Shipper, @Carrier, @Tracking, @BOL, @UserName, @TimeRecord
END

CLOSE tblIn_Cursor;
DEALLOCATE tblIn_Cursor;

SELECT * FROM #TOTAL

DROP TABLE #TOTAL

I've been on this since Sunday. I desperately need help.
Thank you in advance.

[lolafuertes]

In the stored procedure source, you can remove the line that drops the temp table. This is done automatically by SQL.

When you say 'I try to import this sttored procedure' into VS2010 I do not understand what do you try.

The stored procedures can't be imported into a DS. They must be used as a stand alone SQLCommand.

The sintax for the SQL Command can be:

Dim sqlCmd as SQLCommand = new SQLCommand("EXECUTE MyStoredProcedure")

Then it can be used in a select for a data adapter to fill a table, or for a data reader.

In order to try to help you, please post the piece of code where you try to use the stored procedure.

Hope this helps

[bluehangook629]

I was trying to use the sql stored procedure to fill a DataGrid and only way I know is binding the data to the grid. I do use most of my usp as stand alone except for the ones that I use to display data on the datagrid since its performed faster and easier to program.

btw I did solve this problem by changing the temporary table to variable table.
Thanks