Calling resultsets from a storedproc

Question

Hi, I am using SQL 2000. I have a stored proc (spGetApplicantList) which cannot be modified. I need to get the unique LastNameInitials of all the records in that stored proc, so the list of applicants can be sorted alphabetically.

Basically what I need is

Select DISTINCT LEFT(LastName, 1) as [LastNameInitial] from spGetApplicantList Order by LastnameFirstInitial

How can create a new stored proc (spGetLastNameInitial) by using the recordset returned from spGetApplicantList?

I cant seem to get the syntax correct.

TIA

Answer 1

You'd have to execute the first sproc spGetApplicantList into a temp table, and then query that. You can't call stored procedures inline in SELECT statements like that unfortunately.

-- Create temp table, with the schema matching the results of spGetApplicantList 
CREATE TABLE #TempResults
(
LastName VARCHAR(50)
)

INSERT #TempResults
EXECUTE spGetApplicantList

Select DISTINCT LEFT(LastName, 1) as [LastNameInitial] from #TempResults Order by LastnameFirstInitial

DROP TABLE #TempResults

The alternative, is you duplicate the SELECT from the original stored procedure but just do the DISTINCT instead of returning the full resultset - that would save you having to load all the data into a temp table.

Answer 2

This is from this link: Calling SP from SP

1. How can I use results from one stored procedure in another?

So long as the stored procedure produces only a single result, the technique for using the output of one stored procedure in another is pretty straightforward. The technique is to use a temporary table to hold the results of the stored procedure and an INSERT EXEC statement to execute the sproc and save the results. Once the results are in the temporary table they can be used like any other table data. Here's an example procedure that we might like to reuse:

CREATE PROC usp_Demo_AllAuthors as

select * from pubs..authors

GO

Now here's a stored procedure that uses the results of usp_Demo_AllAuthors:

CREATE proc usp_Demo_SPUser as

CREATE TABLE #Authors (
 au_id varchar(11) NOT NULL PRIMARY KEY CLUSTERED,
 au_lname varchar (40) NOT NULL ,
 au_fname varchar (20) NOT NULL ,
 phone char (12) NOT NULL,
 address varchar (40) NULL ,
 city varchar (20) NULL ,
 state char (2) NULL ,
 zip char (5) NULL ,
 contract bit NOT NULL
)

-- Execute usp_Demo_AllAuthors storing the
-- results in #Authors
insert into #Authors
 exec usp_Demo_AllAuthors

-- Here we use the #Authors table.  This example only
-- only selects from the temp table but you could do much
-- more such as use a cursor on the table or join with 
-- other data.
SELECT au_fName + ' ' + au_lname as [name]
     , address+', '+city+', '+state+' '+zip [Addr] 
    from #Authors

DROP TABLE #Authors

GO

—Andrew Novick, SQL Server Expert