将表变量传递给存储过程

I'm trying to pass a table variable to a stored procedure and I get the error: Operand type *: table is incompatible with TY_MyType.

我试图将表变量传递给存储过程，得到的错误是:操作数类型*: table与TY_MyType不兼容。

Here are the relevant pieces of code:

以下是相关的代码片段:

1 - Type Definition

1 -类型定义

CREATE TYPE [dbo].[TY_MyType] AS TABLE(
        [Sampling_ID]               [int]           NULL,
        [Parameter_Name]            [nvarchar](32)  NULL,
        [Measuring_Method_Code]     [int]           NULL,
        [Greater_or_Smaller]        [varchar](1)    NULL,
        [Parameter_Value]           [float]         NULL,
        [Measured_By]               [int]           NULL,
        [No_Measurement_Code]       [int]           NULL,
        [No_Measurement_Comments]   [varchar](512)  NULL,
        [Update_Reason_Code]        [int]           NULL,
        [General_Comment]           [varchar](512)  NULL
) ;

2 - Local table variable declared within the invoking procedure (there is an INSERT that injects data into this local table variable before passing it to another procedure)

2 -在调用过程中声明的本地表变量(在将数据传递给另一个过程之前，有一个INSERT将数据注入到这个本地表变量中)

DECLARE @_l_Tempo_Table TABLE ( Sampling_ID             INT             ,
                                Parameter_Name          NVARCHAR(32)    ,
                                Measuring_Method_Code   INT             ,
                                Greater_or_Smaller      VARCHAR(1)      ,
                                Parameter_Value         FLOAT           ,
                                Measured_By             INT             ,
                                No_Measurement_Code     INT             ,
                                No_Measurement_Comments VARCHAR(512)    ,
                                Update_Reason_Code      INT             ,
                                General_Comment         VARCHAR(512)
                              ) ;

3 - Procedure Declaration

3 -过程说明

CREATE PROCEDURE [p_DATA_Save_Sampling_Results]   (
                                        @_p_Results         [UWQ].[TY_MyType] READONLY   ,
                                        @_p_Result_Code     INT             OUTPUT       ,
                                        @_p_Result_Message  NVARCHAR(2000)  OUTPUT
                                                  ) 
AS
:
:

4 - Procedure Invocation

4 -过程调用

EXEC p_DATA_Save_Sampling_Results   @_l_Tempo_Table             ,
                                    @_l_Result_Code    OUTPUT   ,       -- Integer param
                                    @_l_Result_Message OUTPUT     ;     -- String

The invocation fails with the above mentioned error message, that appears to indicate that there is an inconsistency between the passed and the expected tables, but I can't figure out where such inconcictency could be.

上面提到的错误消息失败了，这似乎表明传递的表和预期的表之间存在不一致，但是我无法确定这种不一致的地方在哪里。

Any suggestion will be highly appreciated.

如有任何建议，我们将不胜感激。

1 个解决方案

#1

You have to declare a variable of your type, fill the data in there, and call the procedure with this type variable, not with a table variable.

您必须声明类型的变量，在其中填充数据，并使用此类型变量调用过程，而不是使用表变量。

like below :

像下图:

DECLARE @@_l_Tempo_Table AS TY_MyType;

you have declared like below

你已经声明如下

DECLARE @_l_Tempo_Table TABLE

See https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

参见https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

#1