Returning to ASP from SQL Server Stored Procedure with a temp table

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

SQL Server 2000 Forums

SQL Server Development (2000)

Returning to ASP from SQL Server Stored Procedure with a temp table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-18 : 09:18:16

Joe writes "Help if you can. I am running a query from ASP. The query is passed through a string i.e. "Exec {stored procedure name} {variable}. The stored procedure is just the query. When it runs, it will return the query no problem.

For example

create procedure futuresGetAll @ifutureid int
as
select * from futures where ifuturesid=@ifutureid

produces a result that can be processed on asp page. (This has been tested and proven successfully.

Here is the issue
If I have a temporary table anywhere in the stored procedure such as

create procedure futuresGetAll @ifutureid int
as
select * into #tempFuture from futures where ifuturesid=@ifutureid
{Additional logic if needed}
select * from #tempFuture
Drop Table #tempFuture

It will run correctly in Query Analyzer but not in the ASP page.
I have been able to test this out and it can prove it.

Can you help??"

mfemenel
Professor Frink

1421 Posts

Posted - 2003-04-18 : 09:28:53

It works great in Query Analyzer because you're actually seeing the result of your select statement. What's happening in ASP though is that you're dropping your table, then trying to return results to page, but there's nothing left to return. The ugly way is to use a global temp, remove the drop table statement and return the results to your page. Call another procedure from your page to drop the temp table. That's a hack solution and isn't going to be reliable or perform well. Probably the better solution would be to rewrite the stored procedure with subqueries instead so that you end up with a selectable result set that you don't have to clean up after. If you get stuck I'm sure you can post your code and we can help you out .

Mike
"oh, that monkey is going to pay"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-04-18 : 09:32:46

That's not exactly accurate Mike

This problem would be solved by putting
SET NOCOUNT ON

at the top of the proc (after the 'AS')

What is happening is the "n records affected" message is being returned as the first recordset. The NOCOUNT setting will disable that and just return your records.

Damian

mfemenel
Professor Frink

1421 Posts

Posted - 2003-04-18 : 09:44:35

D'OH! Yep, you're probably right. Didn't even think of the simple solution. Damn you Merkin! I'm updating my profile right now!

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources