0

Is it possible to use a table as input for a stored procedure?

EXEC sp_Proc SELECT * FROM myTable

I've created a function to return a table consisting of a single record.

ALTER FUNCTION dbo.preEmail
(
 @Num INT,
 @WID INT
)
RETURNS 
@Results TABLE 

(
 WID char(10),
 Company nchar(50),
 Tech nchar(25),
 StartDate datetime,
 Description varchar(max),
 Address varchar(200),
 Phone varchar(15),
 Status varchar(35)

)
AS
BEGIN

INSERT INTO @Results 
    (WID, Company, Tech, StartDate, Description, Address, Phone, Status)

SELECT WID, company, tech, startDate, description, address, phone, status
FROM wo_tbl
WHERE Num = @Number AND wid = @WID


RETURN 
END
GO

Next I have a stored procedure that sends an email to the tech that is scheduled in the above record.

EXEC sp_emailTech @WID, @Company, @Tech, @StartDate, @Description, @Address, @Phone, @Status.

but I'd rather do

EXEC sp_emailTech SELECT * FROM dbo.preEmail(1, 5746)
sonicbabbler
  • 821
  • 1
  • 12
  • 19
  • what version of sql server? – Taryn Aug 12 '13 at 17:00
  • 1
    You could pass the table name in and then perform your operations from dynamic SQL. But why? What are you *really* trying to accomplish? You should ask that question instead of asking how to solve that problem in this specific way. – Aaron Bertrand Aug 12 '13 at 17:01
  • I'm using Microsoft SQL 2005 – sonicbabbler Aug 12 '13 at 17:02
  • The stored procedure fetches 1 record of several fields for a another email sending stored procedure. – sonicbabbler Aug 12 '13 at 17:04
  • 1
    Ok, so really what you need is to execute a stored procedure for every row in a table, right? Could you perhaps update the question to make that more clear, and take out the part about passing a table or query into a stored procedure? – Aaron Bertrand Aug 12 '13 at 17:13

3 Answers3

4

No, you cannot pass a table as a parameter like that.

You could however look at using a Use Table-Valued Parameters (Database Engine) (SQL Server 2008 up)

In your case however it seems that you might be looking at using a DECLARE CURSOR (Transact-SQL) rather.

Do be aware thought that cursor execution does have a performance hit over set-based queries.

Re @Aaron Bertrand comment

DECLARE @id INT,
@name varchar(5)

DECLARE Cur CURSOR FOR
SELECT *
FROM myTable

OPEN Cur

FETCH NEXT FROM Cur INTO @ID, @Name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_Proc @id, @Name
    FETCH NEXT FROM Cur INTO @ID, @Name
END

CLOSE Cur
DEALLOCATE Cur
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • 1
    Why would a cursor be useful here? Could you demonstrate how? – Aaron Bertrand Aug 12 '13 at 17:03
  • @AaronBertrand, I have added an example – Adriaan Stander Aug 12 '13 at 17:06
  • But how does he do that inside the stored procedure? The stored procedure doesn't know the name of the table that has the data that needs to be worked with... – Aaron Bertrand Aug 12 '13 at 17:07
  • I was trying to save myself from typing all that code out. I could just select @ myID = id, @ myName = name and then exec sp_proc @ myId, @ myName – sonicbabbler Aug 12 '13 at 17:08
  • From the question that does not seem to be the problem. It more seems as thought the OP is trying to execute a SP for a rowset (which in this case is a users table). Am I misunderstanding the OPs requirement? – Adriaan Stander Aug 12 '13 at 17:10
  • Yes, maybe, the question is not exactly clear. Thank you for adding an example, it helped clarify the OP's problem, whereas just saying "use a cursor" wasn't helpful to anyone on its own. – Aaron Bertrand Aug 12 '13 at 17:11
0

First, declare a table variable to hold the result. Then, execute the SP with the rigth parameters and hold the result in the previous declared table variable. Then, select the content of this table.

Oscar
  • 13,594
  • 8
  • 47
  • 75
0

You should also give a look to this solved SO thread. Also, I would recommend you to have a look at OPENXML query (pass in table xml and using xpath access the respective field).

See these examples:

Example 1

Example 2

Community
  • 1
  • 1
NeverHopeless
  • 11,077
  • 4
  • 35
  • 56