If you want to retrieve the results of EXECuting a Stored Proc the most common way is to create a temp table and insert the results of EXEC stored proc into this table. The table designs of the temp table and the result of the EXEC storedproc should be the same.
Example:
CREATE TABLE #TEMP
(CONTACTID INT,
CONTACTTITLE VARCHAR(50),
CONTACTFIRSTNAME VARCHAR(50),
CONTACTLASTNAME VARCHAR(50),
FAX VARCHAR(20),
PHONE1 VARCHAR(20),
PHONE2 VARCHAR(20),
EMAIL VARCHAR(50),
CONTACTTYPEID INT,
LOCATIOND INT
)
INSERT INTO #TEMP EXEC wtg_ContactMasterGet 4
SELECT * FROM #TEMP
Here the stored proc wtg_ContactMasterGet takes a parameter 4 and returns a result set. The types of the result set must match with the table design of the temp table.
References: http://www.sqlteam.com/article/return-recordsets-from-dynamic-queries-called-by-exec
