How can I treat a Stored Procedure Result Set as a Table?

Question

I have a stored procedure which returns the best match for a contact email for a customer. Each customer can have many emails and CS is supposed to mark one as the main contact. My stored procedure allows for multiple emails being marked MainContact and for cases where no emails are so marked for a customer. It determines the best choice and returns it as a result set containing FullName and EmailAddress.

I now need to create another stored procedure that will return every OTHER email address for a given customer. I tried the following, but it didn't work. Invalid syntax near "Exec". Is there a way to do this?

My failed SP:

CREATE PROCEDURE [dbo].[PairPalGetAltEmailInfo] @WatchId int
 AS
SELECT FullName, EmailAddress FROM PairPalEmail WHERE (WatchId = @WatchId) and (EmailAddress not in (Exec PairPalGetAltEmailInfo @WatchId))

My Email Table:

CREATE TABLE [dbo].[Email](
	[CustId] [int] NOT NULL,
	[FullName] [varchar](50) NULL,
	[EmailAddress] [varchar](150) NOT NULL,
	[MainContact] [bit] NOT NULL CONSTRAINT [DF_PairPalEmail_MainContact]  DEFAULT (1),
 CONSTRAINT [PK_PairPalEmail] PRIMARY KEY CLUSTERED 
(
	[CustId] ASC,
	[EmailAddress] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Is there a way to do this?

Answer 1

You could either re-write your original SP as an in-line table-valued function...

Or you could do something like this:

CREATE PROCEDURE PairPalGetAltEmailInfo @WatchID int
AS
DECLARE @t TABLE (FullName varchar(50), EmailAddress varchar(150))
INSERT @t EXEC PayPalGetMainEmailInfo @WatchID
SELECT FullName,EmailAddress FROM PairPalEmail WHERE WatchID = @WatchID AND EmailAddress NOT IN (SELECT EmailAddress FROM @t)

---

--Brad (My Blog)

Answer 2

Is there a way to unpropose the answer? Did you test it, Brad? As I recall, you can not insert into table variable, only into temp table using INSERT EXEC syntax.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 3

Is there a way to unpropose the answer? Did you test it, Brad? As I recall, you can not insert into table variable, only into temp table using INSERT EXEC syntax.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Maybe that was a problem in SS 2000, I do not recall well, but it executes fine in 2005 / 2008.

This run ok in my box, with SS 2005 DE sp3.

GO
CREATE PROCEDURE p1
AS
SET NOCOUNT ON;

SELECT 1 AS c1;
GO
DECLARE @t TABLE (c1 int);

INSERT INTO @t(c1)
EXEC p1;

SELECT * FROM @t;
GO
DROP PROCEDURE p1;
GO

AMB

Answer 4

Probably confused with something else -trying to figure out where you can not use table variable but can only do it with temp table -read something somewhere recently. Probably an OpenQuery and in one of SQLUSA responses.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 5

You can't SELECT INTO a table variable... perhaps that's what you're thinking of.

---

--Brad (My Blog)

Answer 6

That would be too simple :) But may be.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 7

I apologize, I neglected to mention that I am running on SS 2000.

When I try Brad's solution, I get the following error:

"EXECUTE cannot be used as a source when inserting into a table variable."

Answer 8

You can use OPENQUERY to make sproc results table like:

SELECT * FROM OPENQUERY(SRVNAME, 'exec sp_who')

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 9

You can use OPENQUERY to make sproc results table like:

SELECT * FROM OPENQUERY(SRVNAME, 'exec sp_who')

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

That is true, but what about when your sp has input parameters?

If you do not know them until execution time, then you need dynamic sql.

There are other cons that you can read in this article.

How to Share Data Between Stored Procedures
http://www.sommarskog.se/share_data.html#OPENQUERY


AMB

Answer 10

I apologize, I neglected to mention that I am running on SS 2000.

When I try Brad's solution, I get the following error:

"EXECUTE cannot be used as a source when inserting into a table variable."

Then use a temporary table. A local one if it is enough.


AMB

Answer 11

Funny, I was right after all for SQL Server 2000.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 12

Funny, I was right after all for SQL Server 2000.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Yes, you were right. That is what I replied in my first post on this thread.


AMB