title: How can I delete rows in the main table that match the ID in my temp table?
body: <p>Below is my SQL code. In the delete section, the object "tblDeletes" that I declared in WITH statement gives me invalid object name, what could be wrong? thank you.   </p>
  
  <pre><code>    USE [NEDB_beta]
  GO
  /****** Object:  StoredProcedure [dbo].[cms_cpAdminDeleteContentItemById]    Script Date: 12/13/2014 12:11:11 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:      &lt;Author,,Name&gt;
  -- Create date: &lt;Create Date,,&gt;
  -- Description: &lt;Description,,&gt;
  -- =============================================
  ALTER PROCEDURE [dbo].[cms_cpAdminDeleteContentItemById] 
  @ID int
  AS
  BEGIN
  WITH    tblDeletes AS 
          (
          SELECT  *
          FROM    [NEDB_beta].[dbo].[cms_tblContent]
          WHERE   ID = @ID -- this condition defines the ultimate ancestors in your chain, change it as appropriate
          UNION ALL
          SELECT  c.*
          FROM    [NEDB_beta].[dbo].[cms_tblContent] as c
          JOIN    tblDeletes
          ON      c.intParentIdDraft = tblDeletes.ID
          )
  SELECT  *
  FROM    tblDeletes
  
  DELETE FROM [NEDB_beta].[dbo].[cms_tblContent] 
  WHERE ID IN 
      (SELECT ID 
       FROM tblDeletes);
  
  END
  </code></pre>