无法使用表输出参数创建存储过程 [英] Can't create stored procedure with table output parameter

问题描述

我有这个代码:

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

但我收到错误关于构造 'READONLY' 的语法不正确".我该如何解决这个问题，因为我真的需要一个 OUTPUT 表参数.

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

推荐答案

表参数是只读的.您无法选择它们.使用表值参数:

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

表值参数必须作为输入 READONLY 参数传递给 Transact-SQL 例程.您不能对例程主体中的表值参数执行 DML 操作，例如 UPDATE、DELETE 或 INSERT.

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.

和表值参数:

您不能在表值参数中返回数据.表值参数仅供输入；不支持 OUTPUT 关键字.

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

阅读SQL Server 中的数组和列表，了解有关替代方案的全面讨论.

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

这篇关于无法使用表输出参数创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！