Can't Create Stored Procedure With Table Output Parameter

I have this code: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProfitDeals]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[GetProfitDealsVar2]

Solution 1:

Table parameters are readonly. You cannot select into them. Use Table-Valued Parameters:

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

And Table-Valued Parameters:

You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

Read Arrays and Lists in SQL Server for a comprehensive discussion on alternatives.

Solution 2:

As Remus states, you can't do that exactly but you could accomplish what you want to achieve by using a User-Defined Function instead: tsql returning a table from a function or store procedure

Which will return your data in a table however I believe you will need to define the table in the Function and not define it as a type