How do I pass a set of values for an optional parameter in an SQL Stored Procedure?

Question

Looking through all of the suggested similar questions, I can find none that addresses my need.

I want to pass in multiple keywords, statuses, funding sources and project IDs. Using the multiple parameters works for a limited number of scenarios but that doesn't seem like a workable solution when there are more options to select from as in a multi-select dropdown box. I read about using "table value parameters," but the implementation isn't clear and I cannot find an example of how to use them in the WHERE statements.

In the code below, I want to be able to pass in multiple "projectStatusID" values so that the where clause would be:

WHERE projectStatusID in (1,3,5,9)

Any help is appreciated.

CREATE PROCEDURE [dbo].[searchProjects_TEST] 

@keyword1 nvarchar(50) = NULL,
@keyword2 nvarchar(50) = NULL,
@keyword3 nvarchar(50) = NULL,
@keyword4 nvarchar(50) = NULL,
@projectStatus int = NULL,
@fundingSourceID int = NULL,
@projectID int = NULL

AS
BEGIN

SET NOCOUNT ON;

SELECT DISTINCT project.projectID
    , projectTitle
    , topicTrackingNumber
FROM project LEFT OUTER JOIN refProjectStatus on refProjectStatus.projectStatusID = project.projectStatusID
    LEFT OUTER JOIN fundingIncrement on fundingIncrement.projectID = project.projectID
WHERE (1=1)
    AND (approvedByID IS NOT NULL)
    AND ((@keyword1 IS NULL OR project.projectTitle LIKE @keyword1)
        OR (@keyword1 IS NULL OR project.description LIKE @keyword1)
        OR (@keyword1 IS NULL OR project.objective LIKE @keyword1))
    AND ((@keyword2 IS NULL OR project.projectTitle LIKE @keyword2)
        OR (@keyword2 IS NULL OR project.description LIKE @keyword2)
        OR (@keyword2 IS NULL OR project.objective LIKE @keyword2))
    AND ((@keyword3 IS NULL OR project.projectTitle LIKE @keyword3)
        OR (@keyword3 IS NULL OR project.description LIKE @keyword3)
        OR (@keyword3 IS NULL OR project.objective LIKE @keyword3))
    AND ((@keyword4 IS NULL OR project.projectTitle LIKE @keyword4)
        OR (@keyword4 IS NULL OR project.description LIKE @keyword4)
        OR (@keyword4 IS NULL OR project.objective LIKE @keyword4))
    AND (@projectStatus IS NULL OR project.projectStatusID = @projectStatus)
    AND (@fundingSourceID IS NULL OR project.fundingSourceID = @fundingSourceID)
ORDER BY projectTitle, topicTrackingNumber
END
GO

This is a kitchen sink. https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example — Aaron Bertrand, Nov 16 '21 at 17:41
@AaronBertrand, I don't think this is a kitchen sink request. It's more like a "kitchen sink from HELL" query. I already have it down where I can search on up to 4 keywords as shown above. What I need is a way to search on multiple "projectStatusID" values when multiple selections are made in a combo box without having to break them out like the "keyword" values. For instance, the user might want to see all projects with projectStatusID in (1,3,5). — R Loomas, Nov 16 '21 at 21:21

score 0 · Answer 1 · answered Nov 18 '21 at 06:13

If you have SQL Server lower version than 2018 you can execute this script for creating a function, which converts delimited strings to table row:

CREATE FUNCTION [dbo].StringSplit
(
    @String  VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN     
 DECLARE @SeparatorPosition INT = CHARINDEX(@Separator, @String ),
        @Value VARCHAR(MAX), @StartPosition INT = 1
 
 IF @SeparatorPosition = 0  
  BEGIN
   INSERT INTO @RESULT VALUES(@String)
   RETURN
  END
     
 SET @String = @String + @Separator
 WHILE @SeparatorPosition > 0
  BEGIN
   SET @Value = SUBSTRING(@String , @StartPosition, @SeparatorPosition- @StartPosition)
 
   IF( @Value <> ''  ) 
    INSERT INTO @RESULT VALUES(@Value)
   
   SET @StartPosition = @SeparatorPosition + 1
   SET @SeparatorPosition = CHARINDEX(@Separator, @String , @StartPosition)
  END    
     
 RETURN
END

Then you can use this function on your stored procedure:

CREATE PROCEDURE [dbo].[searchProjects_TEST] 
    @ID_list varchar(1000)
AS
BEGIN

SET NOCOUNT ON;

    select * from refProjectStatus 
    where projectStatusID in 
        (
            SELECT CAST(Value as integer) FROM StringSplit(ID_list, ',')
        );

END
GO

Example for using: searchProjects_TEST('2,3,5,8,11,20')

How do I pass a set of values for an optional parameter in an SQL Stored Procedure?

1 Answers1