CODEWITHSUNDEEP
sqlsql-serversql-server-2005
sonicbabbler
sonicbabbler

Reputation: 851

Execute a Stored Procedure with a table as input

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)

Upvotes: 0

Views: 13368

Answers (3)

NeverHopeless
NeverHopeless

Reputation: 11233

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166476

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

Upvotes: 4

Oscar
Oscar

Reputation: 13960

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.

Upvotes: 0

Related Questions