I'm trying to make a simple delete in In Memory SQL (Hekaton).

My objective of this native stored procedure is to delete from dbo.SimpleQueue the elements that were passed as parameters (@MsgIds).

I've made several attemps (they are marked as "--"), they all failed because I can't have subqueries on delete statment or because it says I dont have a scaler variable.

CREATEPROCEDURE [dbo].[SimpleRemove](@MsgIds dbo.SimpleDequeueIds READONLY)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC 
WITH( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')   

  --DELETE FROM dbo.SimpleQueue WHERE MsgId IN (SELECT MsgId FROM @MsgIds tvp)     

  --DELETE FROM dbo.SimpleQueue WHERE MsgId IN (@MsgIds)     

  --DELETE sq FROM dbo.SimpleQueue sq INNER JOIN @MsgIds tvp ON tvp.MsgId = sq.MsgId   

  DELETE FROM dbo.SimpleQueue WHERE MsgId IN ('1', '2')

END

So all the attempts to use TVP failed. Is it possible to use TVP to be used in a IN statement?

What are the alternatives? Is to pass the parameter as VARCHAR and create this list of ints "by hand"? Using C# to create a string builder at outputs "'1','2'... '(n)'" ?

Obs: I've made this post here since there is no forum dedicated to In Memory SQL.

UPDATE: I've tried to use VARCHAR as parameter and built it by hand, but it gave me an error: "Error converting data type varchar to bigint." because MsgId is a BigInt

• Edited by João Pedro Antunes 15 hours 44 minutes ago

Two of the statements look OK to me (first and third). Just for completeness I would also try MERGE statement.

Since that SQL Server version is not released yet, perhaps it's a bug and needs to be posted on connect.