在数据库查询中，经常需要根据用户输入的条件来筛选数据。例如，用户可能会输入一个包含多个关键词的字符串，希望在数据库中查找包含这些关键词的记录。这种情况下，简单的'like'查询可能无法满足需求，需要更高级的查询方法。
本文将介绍如何在SQLServer中创建一个存储过程，用于在多个列中查找包含多个子字符串的记录。将通过一个具体的例子来演示这个过程。
假设有一个名为'ContactDetails'的表，包含以下列和记录：
| FirstName | LastName | |
|---|---|---|
| Gary | Lewis | gary@gmail.com |
| Natasha | Williams | natasha@gmail.com |
| Gary | James | gary@live.com |
现在，如果用户输入字符串'gary gmail'进行搜索，那么SQL存储过程应该只返回以下值：
| FirstName | LastName | |
|---|---|---|
| Gary | Lewis | gary@gmail.com |
因为只有gary和gmail同时存在于记录中。
为了完成这个任务，需要在SQLServer中创建一个函数和一个存储过程。首先，需要分割可搜索的字符串，然后创建适当的SELECT SQL来在所需的列中搜索这些字符串。
首先创建一个函数，用于使用特定分隔符分割字符串。以下是C++代码示例：
CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS @t TABLE
(
val VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xml XML
SET @XML = N'
'
+ REPLACE(@s, @sep, ' ')
+ '
通过调用这个函数，可以得到分割后的字符串：
SELECT * FROM dbo.Split(',', 'gary,gmail')
结果将是：
| val |
|---|
| gary |
| gmail |
接下来，将使用这个函数在主要存储过程中。以下是SQL代码示例：
CREATE PROCEDURE [dbo].[sp_FindMultipleStringInTable]
@stringToFind VARCHAR(MAX),
@schema sysname,
@table sysname,
@sqlCommand VARCHAR(MAX),
@SearchColumnName VARCHAR(MAX)
AS
BEGIN
DECLARE @where VARCHAR(MAX)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(MAX)
DECLARE @searchString VARCHAR(MAX)
DECLARE @flg CHAR(1)
BEGIN TRY
SET @SearchColumnName = REPLACE(@SearchColumnName, '"', '''');
SET @sqlCommand = REPLACE(@sqlCommand, '"', '''');
SET @sqlCommand = @sqlCommand + ' where '
SET @where = ''
DECLARE row_cursor CURSOR FOR
SELECT * FROM dbo.split(',', @stringToFind)
OPEN row_cursor
FETCH NEXT FROM row_cursor INTO @searchString
WHILE @@FETCH_STATUS = 0
BEGIN
SET @flg = 'O'
SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM [' + DB_NAME() + '].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''' + @schema + ''' AND TABLE_NAME = ''' + @table + ''' AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') AND COLUMN_NAME IN (' + @SearchColumnName + ')'
EXEC (@cursor)
OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @where <> '' AND @flg <> 'O'
BEGIN
SET @where = @where + ' OR'
END
SET @flg = 'I'
SET @where = @where + ' [' + @columnName + '] LIKE ''%' + @searchString + '%'''
FETCH NEXT FROM col_cursor INTO @columnName
END
CLOSE col_cursor
DEALLOCATE col_cursor
SET @where = @where + ') AND ('
FETCH NEXT FROM row_cursor INTO @searchString
END
CLOSE row_cursor
DEALLOCATE row_cursor
SET @where = SUBSTRING(@where, 0, (LEN(@Where)-5))
SET @sqlCommand = @sqlCommand + '(' + @where
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
GO
创建完这个存储过程后，需要像这样调用它：
EXECUTE dbo.sp_FindMultipleStringInTable
'gary gmail', dbo,
contactdetails,
'Select * from contactdetails',
'',
'FirstName',
'LastName',
'Email'
| FirstName | LastName | |
|---|---|---|
| Gary | Lewis | gary@gmail.com |