在T-SQL中使用任意数量的参数

是否可以创建一个参数化的SQL语句,该语句将采用任意数量的参数？我试图允许用户根据多个关键字过滤列表,每个关键字用分号分隔.因此,输入将类似于"Oakland; City; Planning",WHERE子句将出现与下面相同的内容:

WHERE ProjectName LIKE '%Oakland%' AND ProjectName Like '%City%' AND ProjectName Like '%Planning%'

使用连接创建这样的列表真的很容易,但由于SQL注入漏洞,我不想采用这种方法.我有什么选择？我是否创建了一堆参数,希望用户不要尝试使用我定义的更多参数？或者有没有办法安全地创建参数化SQL？

性能不是什么大问题,因为该表现在只有大约900行,并且不会很快增长,可能每年50到100行.

一个基本的概念验证......实际代码会少一些,但由于我不知道你的表/字段名称,这是完整的代码,所以任何人都可以验证它的工作原理,调整它等等.

--Search Parameters

DECLARE @SearchString VARCHAR(MAX)
SET @SearchString='Oakland;City;Planning' --Using your example search
DECLARE @Delim CHAR(1)
SET @Delim=';' --Using your deliminator from the example

--I didn't know your table name, so I'm making it... along with a few extra rows...

DECLARE @Projects TABLE (ProjectID INT, ProjectName VARCHAR(200))
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 1, 'Oakland City Planning'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 2, 'Oakland City Construction'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 3, 'Skunk Works'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 4, 'Oakland Town Hall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 5, 'Oakland Mall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 6, 'StackOverflow Answer Planning'

--*** MAIN PROGRAM CODE STARTS HERE ***

DECLARE @Keywords TABLE (Keyword VARCHAR(MAX))

DECLARE @index int 
SET @index = -1 

--Each keyword gets inserted into the table
--Single keywords are handled, but I did not add code to remove duplicates
--since that affects performance only, not the result.

WHILE (LEN(@SearchString) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@Delim , @SearchString)  
    IF (@index = 0) AND (LEN(@SearchString) > 0)  
      BEGIN   
        INSERT INTO @Keywords VALUES (@SearchString)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Keywords VALUES (LEFT(@SearchString, @index - 1))   
        SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index))  
      END  
    ELSE 
      SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index)) 
END


--This way, only a project with all of our keywords will be shown...

SELECT * 
FROM @Projects
WHERE ProjectID NOT IN (SELECT ProjectID FROM @Projects Projects INNER JOIN @Keywords Keywords ON CHARINDEX(Keywords.Keyword,Projects.ProjectName)=0)

我决定将几个不同的答案混合成一个:-P

这假设您将传递一个分隔的搜索关键字字符串列表(通过@SearchString传入)作为VARCHAR(MAX),实际上 - 您不会遇到关键字搜索的限制.

每个关键字都从列表中分解出来并添加到关键字表中.您可能希望添加代码以删除重复的关键字,但在我的示例中不会受到影响.效果稍差,因为我们只需要为每个关键字评估一次,理想情况下.

从那里,任何不属于项目名称的关键字都会从列表中删除该项目 ...

因此,搜索"奥克兰"给出了4个结果,但"奥克兰;城市;规划"只给出了1个结果.

您也可以更改分隔符,因此它可以使用空格而不是分号.或者无论你的船漂浮......

此外,由于连接而不是动态SQL,它不像您担心的那样冒着SQL注入的风险.