multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql