469,953 Members | 2,400 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,953 developers. It's quick & easy.

VB doesn't allow temp tables in Stored Procedures?

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

Mar 7 '06 #1
2 2502
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.

Mar 8 '06 #2

Chris Dunaway wrote:
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

Mar 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Tim Pascoe | last post: by
1 post views Thread by msnews.microsoft.com | last post: by
6 posts views Thread by betbubble | last post: by
7 posts views Thread by =?Utf-8?B?TG9zdEluTUQ=?= | last post: by
2 posts views Thread by kizmar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.