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?

First, find out if the problem is in c# or in SQL - try to run the first stored procedure directly from SSMS and see what happens. — Zohar Peled, Oct 07 '19 at 05:40

score 1 · Accepted Answer · answered Oct 07 '19 at 07:32

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:

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
Why not to do it like this:

Snippet:

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

score 1 · Answer 2 · answered Oct 07 '19 at 11:48

Qualify your column names! You think you are writing:

DELETE FROM [dbo].[mytable]
    WHERE NOT EXISTS (SELECT 1
                      FROM @TVP na
                      WHERE mytable.ID = na.ID
                     );

But you are not. The scoping rules of SQL interpret your query as:

DELETE FROM [dbo].[mytable]
    WHERE NOT EXISTS (SELECT 1
                      FROM @TVP na
                      WHERE na.ID = na.ID
                     );

This is rather non-sensical. The WHERE is not correlated to the outer query. It will delete either all rows (if @TVP is empty or the ID is always NULL) or no rows (any other situation).

If you qualify all column references, you will never have this problem.

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

2 Answers2