使用全文搜索可以得到相同的结果。那么你就不必创建动态采石场了。而且由于使用不同的服务，搜索速度非常快。
首先，您必须创建一个fulltextcatalog，然后向其中添加表和列(https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15)
我用AdventureWorksDB写了一个演示。可以根据表和列转换此代码。我刚刚在这里添加了3列，您可以将所有列添加到全文中。

USE [AdventureWorks2016]
GO

/******Object:  FullTextCatalog [SearchEmp]    Script Date: 6/24/2020 8:11:33 AM******/
CREATE FULLTEXT CATALOG [SearchEmp] WITH ACCENT_SENSITIVITY = ON
GO

**strong text**

CREATE FULLTEXT INDEX ON [Person].[Person](
[FirstName] LANGUAGE 'English', 
[LastName] LANGUAGE 'English', 
[MiddleName] LANGUAGE 'English')
KEY INDEX [PK_Person_BusinessEntityID]ON ([SearchEmp], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

  GO

Declare @FirstName Nvarchar(200)='KEN'
Declare @LastName  nvarchar(200)='Sánchez'
Declare @Searchtext nvarchar(400)

-- build search text avoid spaces and special characters in words
SET @Searchtext= concat(TRIM(@FirstName),' OR ', TRIM(@LastName)) 

--- First and Last Name
select top 3 * from [Person].[Person]
where Contains((FirstName,LastName),@Searchtext)

--First Name
SET @Searchtext=@FirstName
select top 3 * from [Person].[Person]
where Contains((FirstName,LastName),@Searchtext)

--Last Name
SET @Searchtext=@LastName
select  top 3* from [Person].[Person]
where Contains((FirstName,LastName),@Searchtext)

-- use ContainsTable
SET @Searchtext=@LastName
select top 3 * from ContainsTable([Person].[Person],(FirstName,LastName),@Searchtext) CT
Inner join [Person].[Person] P ON CT.[Key] = P.BusinessEntityID

您可以使用权重排序结果（sql server加权全文搜索）

像这样的东西对你有用。

SELECT FirstName,
   LastName,
   Address1,
   EmployeeCode,
   OtherFields
   FROM SomeTable
   WHERE ((@FirstName IS NULL) OR ( FirstName = @FirstName)) AND
   ((@LastName IS NULL) OR ( LastName = @LastName)) AND
   ((@Address1 IS NULL) OR ( Address1 = @Address1)) AND
   ((@EmployeeCode IS NULL) OR ( EmployeeCode = @EmployeeCode))

使用表值函数，例如：

CREATE FUNCTION udfSearchEmployee (
    @FirstName varchar(255),
    @Lastname varchar(255),
    ...
)
RETURNS TABLE
AS
RETURN

SELECT FirstName,
       LastName,
       AddressLine,
       EmployeeCode,...
       FROM YourTable
 --To make your query SARGable
       WHERE ((@FirstName IS NULL) OR ( FirstName = @FirstName)) AND
       ((@LastName IS NULL) OR ( LastName = @LastName)) AND
       ((@AddressLine IS NULL) OR ( AddressLine = @Address1)) AND
       ((@EmployeeCode IS NULL) OR ( EmployeeCode = @EmployeeCode)) ...