如何为SQL存储过程中的可选参数传递一组值

Question

浏览所有建议的类似问题，我找不到任何能满足我需求的问题。

我想传入多个关键字、状态、资金来源和项目ID。使用多个参数适用于数量有限的场景，但当有更多选项可供选择时，如在多选下拉框中，这似乎不是一个可行的解决方案。我读到关于使用"；表值参数；但是实现并不清楚，我找不到如何在WHERE语句中使用它们的示例。

在下面的代码中，我希望能够传递多个"；projectStatusID"；值，以便where子句为：

在(1,3,5,9(中的WHERE项目状态ID

感谢您的帮助。

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

Answer 1

如果您的SQL Server版本低于2018，您可以执行此脚本来创建一个函数，该函数将分隔字符串转换为表行：

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

然后你可以在你的存储过程中使用这个功能：

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

使用示例：searchProjects_TEST('2,3,5,8,11,20')