Pass a table variable to a SQL Server stored procedure

Question

I'm trying to pass a table variable to a stored procedure and I get the error:

Operand type clash: table is incompatible with TY_MyType

Here are the relevant pieces of code:

1 - Type Definition

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)

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

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

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 inconsistency could be.

Answer 1

Even if it's probably not as fast as using a table variable you can avoid all the hassles of managing the table type passing the data as json.

In stored procedure foo:

CREATE PROCEDURE [dbo].[foo]() 
AS
BEGIN
   DECLARE @json nvarchar(max)

   SELECT @json = (SELECT [id], [value] 
                   FROM cooltable 
                   FOR JSON PATH)
   EXEC bar @json
END

In stored procedure bar:

CREATE PROCEDURE [dbo].[bar](@json nvarchar(max)) 
AS
BEGIN
    IF (ISJSON(@json) = 1)
    BEGIN
        SELECT [id], [value]
        INTO #all
        FROM OPENJSON (@ids)  
             WITH ([id] varchar(200) '$.id',
                   [value] varchar(200) '$.value') AS a
    END

    --
    -- use #all
    --
END

I strongly suggest to create the temporary table before to insert data into it and create indexes on it appropriately to the use.

I didn't try the code so I may have left some typos in it.