Multiple recordsets from a SP

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

Hi,

I have a stored procedure which creates a temporary table with the results
of a query and , after that, performs a select from the temp table in join
with other tables.

I would like to bind the resultset with a form, but I can't because it's the
second resultset.

Is there a way to achieve this?
Why the first SELECT INTO query returns a resultset? I didn't know that
table creation's query return a resultset.

Any hint? Do I have to perform two distinct SP?

Thank you
 
Use a table variable instead of a temporary table, this should solve your
problem.

Don't forget to set the NoCount option to ON, otherwise a recordset with the
number of affected lines will be returned (this may be the second recordset
that you are seeing). However, even with the NoCount option ON, you still
will have problem with the temporary table because of the complexity of the
metatags information returned by the SQL-Server.

Don't forget that you may also write your SELECT INTO in two different ways.
The second one is used to set up the columns of the temporary table directly
from the select statement instead of defining them before making the select.
This second method will also cause you big trouble if you try to bind this
kind of SP with a form.

S. L.
 
Thank you.

Could you be more precise about using a table variable?

How can I store the result of a SELECT statement into a variable and use it
as a normal table for another query?

I directly created the temp table as a result of the SELECT INTO.
It's better to define the structure of the temp table before and then fill
it with records?

I need to build a lot of SP that require other SP or views to be joined
togheter.
In Access I had to write each query and I had to assemble them into another
"master" query.

I'm trying to exploit the power of T-SQL to improve this, and I would like
to encapsulate everything that is needed into a single SP with many
statements.
This is a good approach? Do you think it's better to use Temp tables or
variable tables? Is there another strategy?

Thank you very much for your help, I'm still studying the new capabilities
of SQL2k programming and I don't know what is best to use.

Bye
 
Hello, news.microsoft.com!
You wrote in message on Mon, 15 Nov 2004 17:24:05 +0100:

nmc> I have a stored procedure which creates a temporary table with the
nmc> results of a query and , after that, performs a select from the temp
nmc> table in join with other tables.

nmc> I would like to bind the resultset with a form, but I can't because
nmc> it's the second resultset.

nmc> Is there a way to achieve this?
nmc> Why the first SELECT INTO query returns a resultset? I didn't know
nmc> that table creation's query return a resultset.

nmc> Any hint? Do I have to perform two distinct SP?

Try to add 'set nocount on' (without apostrophe) at the beginning of your
stored procedure.

With best regards, Igor.
ICQ: 111469481
 
First, you should always define the structure of a temp table before using
it for a better performance, especially when the other option doesn't work
well. In your case, this might or might not be sufficient to solve your
problem.

Second, you can create variable tables with the following syntax:

SET NoCount On

Declare @tbNbMatchsCalendrier table (IdEquipe int, NbMatchs int)

INSERT INTO @tbNbMatchsCalendrier
Select E.IdEquipe, count (*) AS NbMatchs ....

Variable tables are better suited than temp table if the required memory
space is small, for example a few records instead of a billion records.
Temp table are real tables stored in the tmpdb database and, as such,
requires a greater hoverhead.

Finally, for joigning together SP, you should use subqueries or user defined
functions returning tables instead.

S. L.
 
Thank you for your help!

I will try the table variable approach, after your explainations I think
it's the best choice because it is small (less than 1000 records)

Bye
 
Back
Top