Search code examples
sql-servert-sqlproceduretable-variable

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!

Solution

  • 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.