VB doesn't allow temp tables in Stored Procedures?

[Crazy Cat]

Using Visual Basic .NET and SQL Server 2005
I attempt to add a query that is a call to a stored procedure in the
DataSet Designer.

The TableAdapter Query Configuration wizard finds my stored procedure
fine but generates
the following error when I attempt to add the query

Invalid object name '#temp'.

I can tell from the Profiler that VB.NET makes a call to the stored
procedure with NULL values for all the parameters when it attempts to
create the calling function. I have no problem
if I call the procedure from SQL Server Management Studio. Does VB.NET
not allow
temporary tables in Stored Procedures???

Here is the code of the Stored Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- =============================================
ALTER PROCEDURE [dbo].[Set_Switch_Capacity]
-- Add the parameters for the stored procedure here
@ed_market_key varchar(25),
@month int,
@year int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

if @ed_market_key is NULL or @month is NULL or @year is NULL
return

create table #temp (
MSC_KEY varchar(25)
)

insert #temp
select MSC_KEY
from dbo.MSC_VIEW
where ed_market_key = @ed_market_key

-- Insert statements for procedure here
insert dbo.[Statistics]
select MSC_KEY,
@month as [month],
@year as [year],
2 as STAT_KEY,
Capacity
from dbo.Capacity
where not exists (select s.MSC_KEY as MSC_KEY
from dbo.[Statistics] s join #temp t
on s.MSC_KEY = t.MSC_KEY
where [month] = @month
and [year] = @year
and STAT_KEY = 2
)

END

 

[Chris Dunaway]

I'm not sure what your problem is, but the following comes to mind:

1. Try a different name than #temp. Perhaps it doesn't like that
name.
2. Try a table variable instead of a temp table. They use fewer
resources than a temp table and automatically go out of scope when the
proc ends.

 

[Crazy Cat]

I'm not sure what your problem is, but the following comes to mind:

1. Try a different name than #temp. Perhaps it doesn't like that
name.
2. Try a table variable instead of a temp table. They use fewer
resources than a temp table and automatically go out of scope when the
proc ends.

Thanks Chris, creating a table variable did the trick. Now the question
is why?


Thanks again,

Crazy