I want stored procedure A to call stored procedure B, which returns a set of records from a table. Then, I want stored procedure A to perform work on these records. How do I call stored procedure B from within stored procedure A?
In releases earlier than SQL Server 2000, you can call one stored procedure from another and return a set of records by creating a temporary table into which the called stored procedure (B) can insert its results or by exploring the use of CURSOR variables. To maintain efficient query execution plans, you need to recompile the stored procedures either as they create temporary tables or after they insert significant volumes of data into the tables. To minimize recompilations and improve throughput, you should create all temporary tables at the start of the calling stored procedure (A).
Related: T-SQL 101: Stored Procedures
In SQL Server 2000, you can avoid the performance problems of temporary tables by using the new table variable with a user-defined function (UDF). First, you create a table variable in the calling stored procedure (A), then insert the results of the UDF into the table, as Listing 1 shows. Table variables created in stored procedure A are visible (or scoped) only to the defining stored procedure so that other stored procedures can't access them. Scoping table variables to the stored procedure—rather than to the session—reduces the risk of side effects. For example, a called general-purpose stored procedure might try to create an object with the same name, or even worse, drop the table. Scoping also ensures that the variables will be destroyed when they go out of scope (when the stored procedure returns) rather than at the end of the session (which occurs when you use temporary tables). Prompt destruction of the variables releases tempdb space early, especially in client/server applications where the users can be connected in session for many hours.
