Memory-Optimized Table Variable

Question

Azure Managed Instance

Is it possible to pass a Memory-Optimized Table Variable to a child proc and be able to update the table in the child proc?  The examples I’ve found show the Table Variable as READONLY in the child proc.  I want to create the Memory-Optimized Table Variable in dbo.ParentSproc and pass it, and its contents, to dbo.ChildSproc and be able to manipulate the table, then return it to the parent. 
 
Here is an example of what I’m doing, but I’d like ChildSproc to NOT have @mytablevariable as READONLY, so I could run my update (commented out below) in it.

CREATE TYPE [dbo].[CustomerType] AS TABLE(
	[CustomerID] [int] NOT NULL,
	[CustomerName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CustomerCreateDate] [datetime] NOT NULL,
	INDEX [IDX] NONCLUSTERED HASH 
(	[CustomerID] )WITH ( BUCKET_COUNT = 128))
WITH ( MEMORY_OPTIMIZED = ON )
GO

CREATE OR ALTER PROCEDURE dbo.ParentSproc
AS
BEGIN
    DECLARE @mytablevariable AS CustomerType;
    INSERT INTO @mytablevariable VALUES (1, 'Bob', '11/10/2023'), (2, 'Sally', '11/11/2023'), (3, 'John', '11/12/2023');
    EXEC dbo.ChildSproc @mytablevariable;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ChildSproc
    @mytablevariable AS CustomerType READONLY
AS
BEGIN
    SELECT * FROM @mytablevariable;

	--UPDATE @mytablevariable
	--	SET CustomerName = 'Banana'
	--WHERE CustomerID = 1

    --SELECT * FROM @mytablevariable;

END;
GO

EXEC dbo.ParentSproc

Q&A Assist's answer is not ideal. Is it the only way to accomplish this?

Thanks in advance.

Answer 1

Yes, it is possible to pass a Memory-Optimized Table Variable to a child proc and update the table in the child proc. However, the table variable must be declared as READONLY in the child proc. If you want to manipulate the table in the child proc, you can create a new memory-optimized table variable in the child proc and insert the contents of the READONLY table variable into it. You can then manipulate the new table variable and return it to the parent proc. Here is an example of how you can modify your code to achieve this:

CREATE OR ALTER PROCEDURE dbo.ParentSproc
AS
BEGIN
    DECLARE @mytablevariable AS CustomerType;
    INSERT INTO @mytablevariable VALUES (1, 'Bob', '11/10/2023'), (2, 'Sally', '11/11/2023'), (3, 'John', '11/12/2023');
    DECLARE @newtablevariable AS CustomerType;
    INSERT INTO @newtablevariable SELECT * FROM @mytablevariable;
    EXEC dbo.ChildSproc @newtablevariable OUTPUT;
    SELECT * FROM @newtablevariable;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ChildSproc
    @mytablevariable AS CustomerType READONLY,
    @newtablevariable AS CustomerType OUTPUT
AS
BEGIN
    INSERT INTO @newtablevariable SELECT * FROM @mytablevariable;
    UPDATE @newtablevariable
        SET CustomerName = 'Banana'
    WHERE CustomerID = 1;
END;
GO

DECLARE @mytablevariable AS CustomerType;
INSERT INTO @mytablevariable VALUES (1, 'Bob', '11/10/2023'), (2, 'Sally', '11/11/2023'), (3, 'John', '11/12/2023');
DECLARE @newtablevariable AS CustomerType;
INSERT INTO @newtablevariable SELECT * FROM @mytablevariable;
EXEC dbo.ChildSproc @newtablevariable OUTPUT;
SELECT * FROM @newtablevariable;

In the modified code, the parent proc creates a new memory-optimized table variable @newtablevariable and inserts the contents of @mytablevariable into it. The parent proc then calls the child proc with @newtablevariable as an OUTPUT parameter. The child proc inserts the contents of @mytablevariable into a new memory-optimized table variable @newtablevariable, manipulates @newtablevariable, and returns it to the parent proc. The parent proc then selects and returns the contents of @newtablevariable.

---

References:

• Faster temp table and table variable by using memory optimization
• Introduction to Memory-Optimized Tables