I'm in the process of upsizing to SQL 7 from Access 97. I am changing all of the queries to stored procedures. I have a question regarding SQL statement structure for some of my queries that use another queries result set as input to a join. I read that this is accomplished in SQL 7 as a "nested view" but I can't find any examples on how to structure the SQL statements where a stored procedure calls another stored procedure, passing it the same parameters that it has received, and then using those same parameters, does a join to another table passing the combined results back to the calling program.

Any help would be greatly appreciated.

Joe

-- Anonymous, November 12, 2000

Answers

Joe,

Sorry, this is not possible. This would require using a stored procedure as a function. This is not supported in SQL Server 7.0, but is supported in SQL Server 2000.

Hope this helps,

Eric

-- Anonymous, November 12, 2000

One way to do this is to use temp tables. Create the table in the calling procedure and then it can be populated in the called procedure.

This works fine at run time but creates problems at compile time because the temp table does not exist when the called proc is compiled. You need to create the temp table before compiling the proc. Something like:

create table #t member integer; create procedure get_members as insert into #t select member from member_table where age > 21 ;

build a source file with the create table , create proc and drop table .

Compile order is also a consideration when calling other procs. drop table #t

-- Anonymous, January 11, 2001