SQLDataAdapter

I created a DataAdapter, source from store procedure. Why I always get an error if the store procedure have the temporary table ? The error is : Invalid object name : #temptable

Below the script of store procedure

create procedure usp1
(@date1 datetime,
@date2 datetime)

as

create table #temptable
(product_code char(3) null,
product_name varchar(60) null,
createddate datetime)

insert #temptable
select product code, product_name, createddate from product where createddate >= @date1 and createddate <= @date2

select * from #temptable

First, the scope of temporary tables in stored procedures is like a global variable, you can't reference it from outside the stored procedure, unless this table is defined permenantly in the tempdb database (be very careful when u do that), I personnally don't recommend it...

But the good news is, why do u even need to create a temp table !! ...
do the following:

--**************************************
create procedure usp1
(@date1 datetime,
@date2 datetime)

as

select [product code], product_name, createddate from product where createddate >= @date1 and createddate <= @date2

--**************************************


easy enough?! :)

(I meant local varibale , not global, sorry)

Thank you for your reply.

The store procedure's script that I shown to you yesterday is just a sample.

Actually, I've some statements in the middle of the scripts, that cannot be expression in one sql statements.

create procedure usp1
(@date1 datetime,
@date2 datetime)

as

create table #temptable
(product_code char(3) null,
product_name varchar(60) null,
createddate datetime)

insert #temptable
select product code, product_name, createddate from product where createddate >= @date1 and createddate <= @date2

-- The More Statements
Select.........

-- End of more statements

select * from #temptable

Please advise me to solve the problem.

Thank you

There is somethings you could do directly when u select from the original table, and other things u can't.

let me know what are u doing in this *more statments* block..
also, are u expecting to return huge amount of data? or just a small set??

There are a cursors, update statement, insert statement.

The update and insert statements are in Loop Cursor Statement.

DECLARE cur1 CURSOR FOR....
OPEN cur1
FETCH cur1 INTO ...

WHILE @@...
BEGIN
SELECT....
UPDATE....
INSERT.....
END


The result are the records query from more than 1 tables. The rowcount can be more than 1000 records.

Thank U

Don't use temp table to return values, instead,
you could just return a variable of type curser from the stored procedure. so you do your operations on the curser, and just return it...be careful when you use cursers and make sure you deallocate it when you finish....

Also, What's better than all of that, you could create a table in your databse, and use this table only for this stored procedure,,,
then you don't have to user cursers nor temp table..

Happy SP programming!