由于您不能使用表参数（在SQL Server 2008上不是），请尝试传递CSV字符串并让存储过程为您将其拆分为行。
在SQL Server中有很多分割字符串的方法。本文介绍了几乎每种方法的优点和缺点：
Erland Sommarskog撰写的“当表值参数无法剪切时，SQL Server
2005及更高版本中的数组和列表”
您需要创建一个拆分功能。这是分割函数的使用方式：
SELECt * FROM YourTable y INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value
我更喜欢使用数字表方法在TSQL中拆分字符串，但是在SQL Server中拆分字符串的方法有很多，请参见前面的链接，其中解释了每种方法的优点和缺点。
为了使Numbers Table方法起作用，您需要进行一次时间表设置，这将创建一个
Numbers包含1至10,000行的表：
SELECt TOP 10000 IDENTITY(int,1,1) AS Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2ALTER TABLE Numbers ADD ConSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
设置号码表后，请创建以下拆分功能：
CREATE FUNCTION [dbo].[FN_ListToTable]( @SplitOn char(1) --REQUIRED, the character to split the @List string on ,@List varchar(8000)--REQUIRED, the list to split apart)RETURNS TABLEASRETURN ( ---------------- --SINGLE QUERY-- --this will not return empty rows ---------------- SELECt ListValue FROM (SELECt LTRIm(RTRIm(SUBSTRINg(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue FROM ( SELECt @SplitOn + @List + @SplitOn AS List2 ) AS dtINNER JOIN Numbers n ON n.Number < LEN(dt.List2) WHERe SUBSTRINg(List2, number, 1) = @SplitOn ) dt2 WHERe ListValue IS NOT NULL AND ListValue!='');GO
现在，您可以轻松地将以空格分隔的字符串拆分成一个表，然后在表上进行连接或使用，但是您需要使用此代码。这些代码基于OP最新的问题编辑：
CREATE TABLE YourTable (PK int, col1 varchar(20), col2 varchar(20), col3 varchar(20))--data from questionINSERT INTO YourTable VALUES (1,'hello xyz','abc is my last name','and i''m a developer')INSERT INTO YourTable VALUES (2,'hello xyz',null,'and i''m a developer')CREATE PROCEDURE YourProcedure( @keywords varchar(1000))ASSELECt @keywords AS KeyWords,y.* FROM (SELECt t.PK FROM dbo.FN_ListToTable(' ',@keywords) dt INNER JOIN YourTable t ON t.col1 LIKE '%'+dt.ListValue+'%' OR t.col2 LIKE '%'+dt.ListValue+'%' OR t.col3 LIKE '%'+dt.ListValue+'%' GROUP BY t.PK HAVINg COUNT(t.PK)=(SELECt COUNT(*) AS CountOf FROM dbo.FN_ListToTable(' ',@keywords)) ) dt INNER JOIN YourTable y ON dt.PK=y.PKGO--from question EXEC YourProcedure 'xyz developer'-- returns 2 rowsEXEC YourProcedure 'xyz abc'-- returns 1 rowEXEC YourProcedure 'abc developer'-- returns 1 rowEXEC YourProcedure 'hello'-- returns 2 rowsEXEC YourProcedure 'hello developer'-- returns 2 rowsEXEC YourProcedure 'xyz'-- returns 2 rows输出：
KeyWords PK col1 col2 col3-------------- ----- ---------- -------------------- --------------------xyz developer 1 hello xyz abc is my last name and i'm a developerxyz developer 2 hello xyz NULL and i'm a developer(2 row(s) affected)KeyWords PK col1 col2 col3-------------- ----- ---------- -------------------- --------------------xyz abc 1 hello xyz abc is my last name and i'm a developer(1 row(s) affected)KeyWords PK col1 col2 col3-------------- ----- ---------- -------------------- --------------------abc developer 1 hello xyz abc is my last name and i'm a developer(1 row(s) affected)KeyWords PK col1 col2 col3-------------- ----- ---------- -------------------- --------------------hello 1 hello xyz abc is my last name and i'm a developerhello 2 hello xyz NULL and i'm a developer(2 row(s) affected)KeyWords PK col1 col2 col3--------------- ----- ---------- -------------------- --------------------hello developer 1 hello xyz abc is my last name and i'm a developerhello developer 2 hello xyz NULL and i'm a developer(2 row(s) affected)KeyWords PK col1 col2 col3-------------- ----- ---------- -------------------- --------------------xyz 1 hello xyz abc is my last name and i'm a developerxyz 2 hello xyz NULL and i'm a developer(2 row(s) affected)
