MSSQL Stored Procedures With Temp Tables and .Net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

MSSQL Stored Procedures With Temp Tables and .Net

Author

Topic

SergeB
Starting Member

3 Posts

Posted - 2005-06-14 : 10:05:42

Greetings,

I've come across a little snag and I'm not sure how to approach it, and still being in the beginner stage for both .net and sql my capabilities are limited. :)

I have a stored procedure on MSSQL 2k that creates 2 temporary tables followed by a select statement. The select qualifies unique records from the second table against the first table based on 4 criterias.

Unfortunately, when I try to create a dataset in asp.net based on a dataadapter that calls the stored procedure it tells me that the 2 temp tables are invalid objects and dissallows it. (It also doesn't let me use the return records from the select statement)

If I display the data by right clicking on the dataadapter and choosing preview data it returns the proper data.

Is there a way to do this? Is the problem that once the select statemeent completes the temporary tables are flushed (seeing as they are active for the stored procedure session only)?

** I forgot to mention that there are 2 variables being passed to the stored procedure. 1 for each table.

Thank you,

Serge

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-14 : 10:59:21

quote:

seeing as they are active for the stored procedure session only

Yes,Can you post the code used in stored Procedure?

Madhivanan

Failing to plan is Planning to fail

SergeB
Starting Member

3 Posts

Posted - 2005-06-14 : 11:13:08

quote:
Originally posted by madhivanan

quote:

seeing as they are active for the stored procedure session only

Yes,Can you post the code used in stored Procedure?

Madhivanan

Failing to plan is Planning to fail

Sure, here is the code with modifications on names

IF OBJECT_ID('TempDB..##temptbl') IS NOT NULL
drop table ##temptbl;
select distinct cola, colb, colc, cold, cole, colf, colg, cola + colb + colc + cold as duplkey
into ##temptbl
from table1, table2, table3, table4
where table1.cola = table2.colakeyid
and table1.colb = table3.colbkeyid
and table1.colf = table4.colfkeyid
and colc < @value

IF OBJECT_ID('TempDB..##temptbl2') IS NOT NULL
drop table ##temptbl2;
select distinct cola, colb, colc, cold, cole, colf, colg, cola + colb + colc + cold as duplkey
into ##temptbl2
from table1, table2, table3, table4
where table1.cola = table2.colakeyid
and table1.colb = table3.colbkeyid
and table1.colf = table4.colfkeyid
and colc = @value

select distinct cola, colb, colc, cold, cole, colf, colg
from ##temptbl2
where ##temptbl2.duplkey not in
(select ##temptbl.duplkey
from ##temptbl)

Serge

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-15 : 02:17:26

Dont use temporary tables
Create two tables(say table1, table2) in your database with same structure as that of that temporary tables
Now in your procedure, write


Delete from table1
Insert into table1 select distinct cola, colb, colc, cold, cole, colf, 
colg, cola + colb + colc + cold as duplkey
from table1, table2, table3, table4
where table1.cola = table2.colakeyid 
and table1.colb = table3.colbkeyid
and table1.colf = table4.colfkeyid
and colc < @value
--Do the same for table2
--Write other queries based on that tables

Madhivanan

Failing to plan is Planning to fail

SergeB
Starting Member

3 Posts

Posted - 2005-06-16 : 16:39:05

quote:
Originally posted by madhivanan

Dont use temporary tables
Create two tables(say table1, table2) in your database with same structure as that of that temporary tables
Now in your procedure, write

Delete from table1
Insert into table1 select distinct cola, colb, colc, cold, cole, colf,
colg, cola + colb + colc + cold as duplkey
from table1, table2, table3, table4
where table1.cola = table2.colakeyid
and table1.colb = table3.colbkeyid
and table1.colf = table4.colfkeyid
and colc < @value
--Do the same for table2
--Write other queries based on that tables

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan. I switched to the normal tables (even though I was trying to avoid non permanent ones) and getting the results I need.

Serge

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources