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

score 1 · Answer 1 · edited May 23 '17 at 12:20

Check out the selected answer in this post: How to pass string parameter with `IN` operator in stored procedure SQL Server 2008

What the user does is creates a stored procedure that will split apart the comma separated string and then return a table. Once you have the table of ID's, you can plug it into your queries. It might take a little modification to get it to work since yours contains more queries, but the theory is still the same.

score 1 · Answer 2 · answered Sep 06 '13 at 13:18

If you cannot use a table parameter, then create a delimited string parameter to hold the multiple Id values and convert that delimited string into a table variable inside the proc. (There are many T-SQL UDFs available online that do that. One good article is this one.)

Then your In clause will look like this:

  Where [Somecolumn] In (Select funcColName From dbo.ConvertFunction(@stringParameter))

score 1 · Accepted Answer · answered Sep 06 '13 at 13:29

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

I think a couple of the other answers were more elegant, Hedinn has gone to great lengths to answer this for me. I really appreciate his effort, and those of the other respondents. I understand now what to do. I'll likely use the SP or UDF to parse out a list. I already have a utility sub in my Access app that creates the comma delimited list. Again, thanks, guys. I never expected this many comments! — JimS-CLT, Sep 06 '13 at 13:42

score 0 · Answer 4 · answered Sep 06 '13 at 13:15

0

You could look at using a table value parameter...

answered Sep 06 '13 at 13:15

Martin Milan

6,346
2
32
44

I don't think you can pass a table value parameter to a SP – Hedinn Sep 06 '13 at 13:34

Dan · Answer 5 · 2013-09-06T13:22:35.373

Although it's possible to use table valued parameters in SQL Server, there's no way easy way of populating those parameters from Access, as far as I know.

A solution could be to use dynamic SQL inside your Stored Procedure, and then having the project ID's in a varchar(max) that is created inside your Access application, for example:

@projectIdList = '200,300,400'

Then in your dynamic SQL you would just include it as

'WHERE tblProjects.ID IN (' + @projectIdList + ')'

This is obviously vulnerable to SQL inject attacks, but depending on how your Access application is used, this might be acceptable.

Another, more safe solution, is to allow Access to write the projectId's as rows in a small table before calling the stored procedure, and then inside the stored procedure, do the deletes like:

DELETE FROM tblprojects 
WHERE tblProjects.ID IN (SELECT ProjectId FROM ProjectIdsToBeDeleted)

This is a lot safer and cleaner, since you avoid the dynamic SQL. However, you need some way to handle concurrency - perhaps by generating a unique identifier when populating the ProjectIdsToBeDeleted table, and then filtering on that same identifier when executing the stored procedure.

Parameter to populate an "IN" clause

5 Answers5