SQL Table Value Parameter only works when its data is copied to temporary table

Question

I'm passing the data through c# to sql stored procedure using dapper.

c# (using dapper):

 var dt = new DataTable("dbo.TVPtype");
 dt.SetTypeName("dbo.TVPtype");
 dt.Columns.Add("ID", typeof(int));
 dt.Rows.Add(5);
_db.Execute("mysp", param: new { TVP = dt.AsTableValuedParameter("dbo.TVPtype"), commandType: CommandType.StoredProcedure);

The bellow SQL query will not work and I don't know Why! And it removes all data from mytable. SQL stored procedure:

CREATE PROCEDURE [dbo].[mysp] @TVP dbo.TVPtype READONLY AS
DELETE FROM [dbo].[mytable] WHERE NOT EXISTS
(
SELECT NULL FROM @TVP na
WHERE ID = na.ID
)

To solve the problem I've used a temporary table in the stored procedure like bellow, and it works well. My solution using temporary table:

CREATE table temptb (ID int)
insert into temptb select * from @TVP

Now I use temptb instead of @TVP in the delete query:

DELETE FROM [dbo].[mytable] WHERE NOT EXISTS
(
SELECT NULL FROM temptb na
WHERE ID = na.ID
)

It works well and delete specific data (not all data) ! So what's wrong with my first query?

Answer 1

I can't tell you, why it's working with temporary table, but the problem comes from the DELETE statement if you change it to:

DELETE t1 
FROM [dbo].[mytable] AS t1 WHERE NOT EXISTS
(
    SELECT NULL FROM @TVP na
    WHERE t1.ID = na.ID
)

It will work. See, that when I add alias to mytable condition becomes clear which ID's it should compare. When in your example it might compare @TVP ID with it self(that's my guess).

I want to add two more points to your query:

1. It's better to select something that is not NULL when you check for existence, as NULL is special in SQL(although it is working in your example). For example SELECT 1 FROM @TVP... would be more readable

2. Why not to do it like this:

Snippet:

DELETE FROM [dbo].[mytable] AS t1 WHERE ID NOT IN
(
    SELECT na.ID FROM @TVP na
)