nested queries, stored procedures, temporary table

[ahokdac-sql]

Hi,
I'm adapting access queries to sql server and I have difficulties with
the following pattern :
query1 : SELECT * FROM Query2 WHERE A=@param1
query 2: SELECT * FROM Table2 WHERE B=@param2

The queries are nested, and they both use parameters.
In MS Acccess the management of nested queries with parameters is so
easy (implicit declaration of parameters, transmission of parameters
from main query to nested query)
that I don't know what the syntax should be for stored procedures.

The corresponding stored procedure would be something like

CREATE TABLE #TempTable (...table definition...)
INSERT INTO #TempTable ExecProc spQuery2 @Param2
SELECT * FROM #TempTable WHERE A=@Param1

And spQuery2 would be : SELECT * FROM Table2 WHERE B=@Param

I was wondering if this syntax would work and if I can skip the
explicit declaration of #TempTable definition.

Thanks for your suggestions.

[David Portas]

I don't understand why you would want to use a temp table at all. In
SQL Server you can nest queries in the FROM clause as derived tables
but in your case it looks like you could just combine the two WHERE
clauses.

--
David Portas
SQL Server MVP
--

[David Portas]

I don't understand why you would want to use a temp table at all. In
SQL Server you can nest queries in the FROM clause as derived tables
but in your case it looks like you could just combine the two WHERE
clauses.

--
David Portas
SQL Server MVP
--

[Stu]

You can use nested subqueries in SQL Server; you can even have multiple
result set returned from a single stored procedure (if you wish).
Based on your example, I'm assuming you want the results from query1 to
be your final result; in that case, you can do something like:

SELECT colList
FROM (SELECT colList FROM Table2 WHERE B=@Param) alias
WHERE A=@Param1

If you have to have a stored procedure for query2, you are correct that
you will need to insert the results from query2 into a temporary table
or table variable and then query those results.

HTH,
Stu

[ahokdac-sql]

I do agree with both of you that there are other ways to perform the
queries.
But for readability and commonalization purposes I do need to have a
stored procedure for query 2.
I need some pointers for the syntax :
How do I declare the temp table or table variable ?
How do I load the table with the result of the stored procedure ?

Thanks a lot,

[ahokdac-sql]

Actually I found what I was looking for at
http://www.sommarskog.se/share_data.html#UDF

Syntax would be
CREATE FUNCTION spQuery2(@Param varchar(30))
RETURNS TABLE
AS
RETURN (SELECT * FROM Table2 WHERE B=@Param)
then spQuery1 would be
SELECT * FROM spQuery2(@Param2) WHERE A=@Param1

I'm sure that they're ways to make it more efficient, etc. but that one
is a good start.

Thanks