sqlt-sqlstored-proceduresms-access-2007

Parameter to populate an "IN" clause


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

Solution

  • 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