如何在存储过程中实现 sql 搜索功能(Sql Server 2008)

问题描述

我需要编写一个存储过程，根据可选参数使用 sql server 2008 搜索表.

I need to write a stored procedure that will search a table based on optional parameters Using sql server 2008.

会有两种模式

• 基本搜索模式(我们只是传递一些文本)

• 高级搜索模式(使用可选参数，不使用 SearchText.)

• basic search mode (we just pass some text)

• advanced search mode (Optional parameters are used NOT SearchText is used.)

为了测试，我使用 AdventureWorks.Person.Contact 表

For testing I am using the AdventureWorks.Person.Contact table

你会写如下内容吗如果没有你能提出如何改进它的建议吗?非常感谢

Would you write something like below if not can you make suggestions how to improve it?Thanks a lot

ALTER PROCEDURE SearchPeople
@SearchText nvarchar(200)=NULL,   --- only used in basic search mode
@SearchMode bit,
@FirstName varchar(50)=NULL,
@LastName varchar(50)=NULL,
@EmailAddress varchar(50)=NULL,
@Phone nvarchar(25)=NULL

作为

IF @SearchMode=0
    BEGIN
    print 'BASIC SEARCH'
        SELECT *
        FROM   [Person].[Contact]
        WHERE  (FirstName LIKE '%' + @SearchText + '%'
        OR LastName LIKE '%' + @SearchText + '%'
        OR EmailAddress LIKE '%' + @SearchText + '%'
        OR Phone LIKE '%' + @SearchText + '%')

    END

ELSE
   BEGIN

    print 'ADVANCED SEARCH'

        SELECT *
        FROM   [Person].[Contact]
        WHERE  (FirstName =@FirstName OR @FirstName IS NULL)
        AND (LastName =@LastName OR @FirstName IS NULL)
        AND (EmailAddress =@EmailAddress OR @EmailAddress IS NULL)
        AND (Phone =@Phone OR @Phone IS NULL)

    END

推荐答案

我同意 Joe 的看法.您的解决方案会导致参数嗅探.解决参数嗅探的一种方法是拆分为基本和高级搜索存储过程.但即便如此，您仍需要在高级搜索存储过程中使用动态 SQL 以避免参数嗅探.我不知道你的具体情况，但如果你只有一两个字段可以搜索，那么也许你不需要担心参数嗅探，但是如果你有超过 5 或 6 个参数，你绝对应该去使用动态 SQL.

I agree with Joe. Your solution would lead to parameter sniffing. one way to solve param sniffing is to split into basic and advance search stored procedure. But even then you would need to use Dynamic SQL in the advanced search stored procedure to avoid parameter sniffing. I dont know about your specific situation, but if you have just one or two fields to search by, then maybe you dont need to worry about param sniffing, but if you have , lets say, more than 5 or 6 parameters you should definitely go with dynamic SQL.

所以高级搜索应该是这样的.

so the advanced search should look something like this.

DECLARE @query VARCHAR(MAX);

SET @query = 'SELECT *
              FROM [Person].[Contact]
              WHERE 1=1 '

IF @FirstName IS NOT NULL
 SET @query = @query + ' AND FirstName = @FirstName '

IF @LastName IS NOT NULL
 SET @query = @query + ' AND LastName = @LastName '

IF @EmailAddress IS NOT NULL
 SET @query = @query + ' AND EmailAddress = @EmailAddress '

IF @Phone IS NOT NULL
 SET @query = @query + ' AND Phone = @Phone '

sp_executesql @query,
              N'@FirstName VARCHAR(50),
                @LastName VARCHAR(50),
                @EmailAddress VARHCAR(50),
                @Phone NVARCHAR(25)',
              @FirstName,
              @LastName,
              @EmailAddress,
              @Phone

这是一篇关于参数嗅探的有用文章http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

Here is a usefull article on parameter sniffing http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

这篇关于如何在存储过程中实现 sql 搜索功能(Sql Server 2008)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！