Parameter to populate an "IN" clause

Question

In this procedure, the user passes one project ID. I'd like to be able to pass an indeterminate number of project IDs so I can change the where clauses from "ProjectID=@projectID" (for example) to "ProjectID IN (@ProjectID)".

That way, I can delete a dozen projects with one call to the DB, rather than calling the DB repeatedly.

Is there a good strategy for doing that? I'm calling the SP from Access....

create procedure dbo.cpas_DeleteProject
@ProjectID INt = 0,
@errorFlag int OUTPUT
AS
set @errorFlag=0
BEGIN TRY
    BEGIN TRANSACTION
        DELETE FROM tblWOTasks WHERE tblWOTasks.WorkOrderID IN (SELECT ID FROM tblWorkOrders WHERE ProjectID=@ProjectID)
        DELETE FROM tblELaborSpread WHERE tblELaborSpread.WorkOrderID IN (SELECT ID FROM tblWorkOrders WHERE ProjectID=@ProjectID)
        DELETE FROM tblWorkOrders WHERE tblWorkOrders.ProjectID IN (SELECT ID FROM tblWorkOrders WHERE ProjectID=@ProjectID)
        DELETE FROM tblCPTransactiON WHERE tblCPTransactiON.CPProjectID=@ProjectID
        DELETE FROM tblCPJE WHERE tblcpje.jeid IN 
            (SELECT tblcpje.JEID FROM tblCPJE left joIN tblCPTransactiON as CR ON CR.CPTransID = tblCPJE.JECreditID 
                            left joIN tblCPTransactiON as DR ON DR.CPTransID = tblCPJE.JEDebitID 
                    WHERE DR.CPTransID is null AND cr.CPTransID is null)        
        DELETE FROM tblProjectTasks WHERE tblProjectTasks.ProjectID=@ProjectID
        DELETE FROM xrefProjectMICAP WHERE xrefProjectMICAP.ProjectID=@ProjectID
        DELETE FROM tblworkorders WHERE tblWorkOrders.ProjectID=@ProjectID
        DELETE FROM tblprojects WHERE tblProjects.ID=@ProjectID
        --Project Comments cascade delete....
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    set @errorFlag=1
END CATCH

Answer 1

The only way I have been able to pass multible values to a stored procedure is to concat them to a string and pass the string to the SP and then parse the string in the beginning of the SP, something like this (the projetc_id's are delimeted with ,

CREATE PROCEDURE [dbo].[cpas_DeleteProject] 

@ProjectID varchar(3000)
@errorFlag int OUTPUT
AS

--Parse function
DECLARE @tblstring Table(ProjectID int) --table variable to store all parsed ProjectID's
DECLARE @project varchar(10)

DECLARE @StartPos int, 
@Length int,--streng lengd
@Delimeter varchar(1)=','--delimeter
WHILE LEN(@ProjectID) > 0
  BEGIN
   SET @StartPos = CHARINDEX(@Delimeter, @ProjectID)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@ProjectID) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
  BEGIN
    SET @Project = SUBSTRING(@ProjectID, 1, @StartPos - 1)
    SET @ProjectID = SUBSTRING(@ProjectID, @StartPos + 1, LEN(@ProjectID) - @StartPos)
  END
ELSE
  BEGIN
    SET @Project = @ProjectID
    SET @ProjectID = ''
  END
INSERT @tblstring (ProjectID) VALUES(@Project)
END


     set @errorFlag=0
BEGIN TRY
BEGIN TRANSACTION
    DELETE FROM tblWOTasks WHERE tblWOTasks.WorkOrderID IN (SELECT ID FROM tblWorkOrders WHERE ProjectID in (SELECT ProjectID  from @tblstring))
    DELETE FROM tblELaborSpread WHERE tblELaborSpread.WorkOrderID IN (SELECT ID FROM tblWorkOrders WHERE ProjectID in (SELECT ProjectID  from @tblstring))
    DELETE FROM tblWorkOrders WHERE tblWorkOrders.ProjectID IN (SELECT ID FROM tblWorkOrders WHERE ProjectID in (SELECT ProjectID  from @tblstring))
    DELETE FROM tblCPTransactiON WHERE tblCPTransactiON.CPProjectID in (SELECT ProjectID  from @tblstring)
    DELETE FROM tblCPJE WHERE tblcpje.jeid IN 
        (SELECT tblcpje.JEID FROM tblCPJE left joIN tblCPTransactiON as CR ON CR.CPTransID = tblCPJE.JECreditID 
                        left joIN tblCPTransactiON as DR ON DR.CPTransID = tblCPJE.JEDebitID 
                WHERE DR.CPTransID is null AND cr.CPTransID is null)        
    DELETE FROM tblProjectTasks WHERE tblProjectTasks.ProjectID in (SELECT ProjectID  from @tblstring)
    DELETE FROM xrefProjectMICAP WHERE xrefProjectMICAP.ProjectID in (SELECT ProjectID  from @tblstring)
    DELETE FROM tblworkorders WHERE tblWorkOrders.ProjectID in (SELECT ProjectID  from @tblstring)
    DELETE FROM tblprojects WHERE tblProjects.ID in (SELECT ProjectID  from @tblstring)
    --Project Comments cascade delete....
COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
set @errorFlag=1
END CATCH