Can't create stored procedure with table output parameter

24,048

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

Share:
24,048
Relrin
Author by

Relrin

Updated on July 29, 2022

Comments

  • Relrin
    Relrin almost 2 years

    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]
    GO
    
    IF EXISTS(SELECT 1 FROM sys.types WHERE name = 'TableOrderType' AND is_table_type = 1 AND SCHEMA_ID('dbo') = schema_id)
    DROP TYPE [dbo].[TableOrderType];
    
    CREATE TYPE TableOrderType AS TABLE(
        Order_ID int NOT NULL,
        Order_AccNumber int NOT NULL,
        Order_OpenDate datetime NULL,
        Order_CloseDate datetime NULL,
        Order_Profit float NULL
    );
    GO
    
    CREATE PROCEDURE [dbo].[GetProfitDeals](@OpenDate datetime = NULL, @CloseDate datetime  = NULL, @MinProfit float = NULL, @out TableOrderType OUTPUT READONLY)
    AS
        INSERT INTO @out
        SELECT * FROM [Orders]
        WHEN [Orders].[OpenDate] >= @OpenDate
    GO
    

    But I get the error "Incorrrect syntax about construction 'READONLY'". How I can fix this, because me I really need a OUTPUT table parameter.