T-SQL Delete command basing on table variable

Question

Hi, I need to delete some rows from table where indexes are equal indexes in table variable

  declare @m_table as table
  (
     number NUMERIC(18,0)
  )
...
inserting some rows into @m_table
...
DELETE ct FROM [dbo].[customer_task] ct
          inner join project_customer pc on pc.id_customer = @m_table.number
          inner join customer_user cu on cu.id_project_customer = pc.id
WHERE ct.id_csr_user = cu.id AND ct.id_status = 1;

but this code generates an error: Must declare the scalar variable "@m_table" How to solve that ?

Answer 1

You probably have a 'GO' (a batch separator) in those '...'

Variable declarations do not span batches.

Answer 2

The error means that SQL is expecting you to treat @m_table like a standard table, rather than a scalar (int, bit, etc.) variable. Perhaps something like this will work?

DELETE ct FROM [dbo].[customer_task] ct
WHERE ct.id_csr_user IN (
    SELECT cu.id FROM customer_user cu
    INNER JOIN project_customer pc ON pc.id = cu.id_project_customer
    WHERE pc.id_customer IN (SELECT number FROM @m_table.number)
) AND ct.id_status = 1;