Very Computer

If I have a stored proc that comprises of the following steps:

Step 1. Generate some sql on the fly and insert result set into temp
table 1
Step 2. Generate some more sql on the fly and insert result set into
temp table 2
Step 3. Select from joins on temp tables and other result sets not
involving temp tables

The stored proc returns the expected result set ok as I executed the
stored proc in query analyzer.

Can I base an asp page on this result set or is the result set lost as
soon as the stored procedure is executed, its just that when I called
a function from my asp page that returns the result set I get an error
"Object reference not set to an instance of an object" which suggest
nothing is getting returned. I'm just not sure as to the status of
temp tables. I have another stored proc which is similar in that it
involves temp tables but all it returns is a row count as an ouput
parameter.

Thanks

Paul

The short answer is Yes, you can base an ASP on the result set returned from
a stored procedure, even if the result set uses temp tables in its
generation. However without additional information, it is impossible, at
least for me, to provide any additional assistance.

Try simplifying the situation as much as you can, and post the simplified
code ... the less the better, because no one is likely to look through
hundreds of lines of SQL and ASP coding.

BTW, while I do not personally do ASP coding, I have worked on projects
where ASP pages were based on result sets returned from stored procedures I
coded ... point being that I might not be able to help you with the ASP
part.

One last thought ... if you are using stored procedures, be sure to add a
SET NOCOUNT ON at the beginning of the procedure. Otherwise, the "x row(s)
affected" is returned as the first result set, and that throws a lot of
developers   :-)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> Hi,

> If I have a stored proc that comprises of the following steps:

> Step 1. Generate some sql on the fly and insert result set into temp
> table 1
> Step 2. Generate some more sql on the fly and insert result set into
> temp table 2
> Step 3. Select from joins on temp tables and other result sets not
> involving temp tables

> The stored proc returns the expected result set ok as I executed the
> stored proc in query analyzer.

> Can I base an asp page on this result set or is the result set lost as
> soon as the stored procedure is executed, its just that when I called
> a function from my asp page that returns the result set I get an error
> "Object reference not set to an instance of an object" which suggest
> nothing is getting returned. I'm just not sure as to the status of
> temp tables. I have another stored proc which is similar in that it
> involves temp tables but all it returns is a row count as an ouput
> parameter.

> Thanks

> Paul

Thanks for the reply. I eventually found my mistake - it turned out to
be a simple asp error rather than a sql error. I'm not too familiar
with temp tables in sql server and was my primary concern initially.
Thanks again though.

Paul