CODEWITHSUNDEEP
sql-servert-sqlproceduretable-variable
BlackViking
BlackViking

Reputation: 53

Stored Procedure that has table argument in T-SQL

Table Argument as OUTPUT

I want to pass a table variable into a procedure that has table argument as output, but not as read only! I want to be able to modify that argument inside the PROC. Is this possible? If it's not possible, is there another way to do this?

thanks!

Upvotes: 0

Views: 1617

Answers (1)

gbn
gbn

Reputation: 432230

You'd have to copy the table valued parameter into a table variable or temp table

CREATE PROC DoStuff
    @tvp SomeTableType READONLY
AS
..
SELECT * INTO #LocalCopy FROM @tvp; -- take local copy
...
DoStuff -- do processing on the input
...
SELECT ... FROM LocalCopy;  --return results to client
GO

After comment, a table valued parameter can not be declared OUTPUT. From CREATE PROC

A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

Upvotes: 1

Related Questions