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.

Thank you @ZoharPeled , but I get an error near `AS` - illegal syntax I guess. — FDavidov, Jun 18 '17 at 10:19
Sorry, forgot the schema. Declare the variable the same as ypu declared the parameter in the stored procedure, just without the readonly. — Zohar Peled, Jun 18 '17 at 10:32

score 20 · Answer 1 · edited Jul 14 '21 at 17:32

20

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://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine for examples, like the following:

/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
   SELECT Name, 0.00
   FROM AdventureWorks2012.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

edited Jul 14 '21 at 17:32

ruffin

16,507
9
88
138

answered Jun 18 '17 at 10:11

IngoB

2,552
1
20
35

Thank you IngoB for your answer, but: (1) The reference you sent is for version 2016 while in my case it is 2012, and (2) I need to pass a TABLE to the procedure and not a single record. – FDavidov Jun 18 '17 at 10:19
Thank you @TheGameiswar. The second question remains; I need to pass a number of records, not a single one. Si I created a table variable within the first procedure matching the same declaration of the TYPE, inserted records into it, and passed it as a parameter. That is where the error condition is triggered. – FDavidov Jun 18 '17 at 10:28
you can check the link referenced it has examples – TheGameiswar Jun 18 '17 at 10:29
Sure, you can insert rows into the type variable. :) – IngoB Jun 18 '17 at 10:47

Max Favilli · Answer 2 · 2020-10-09T14:41:40.557

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.

Up vote, this solved my problem with deadlock described in https://sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock#0 — Oyun, Jun 23 '23 at 07:39

Pass a table variable to a SQL Server stored procedure

2 Answers2