Geek Answers Handbook

Passing a separator string to a stored procedure to search the database

How to pass a string marked with a space or comma to the stored procedure and the filtering result? I'm trying to do something like -

Parameter Value -------------------------- @keywords key1 key2 key3

Then the procedure that I want first is stored

  • find all records with first or last name as key1
  • filter step 1 with first or last name as key2
  • filter step 2 with the first or last name, for example key 3

Another example:

 col1 | col2 | col3 ------------------------------------------------------------------------ hello xyz | abc is my last name | and i'm a developer hello xyz | null | and i'm a developer

If I am looking for any follow-up, should it return for everyone?

  • "xyz developer" returns 2 rows

  • "xyz abc" returns 1 row

  • "abc developer" returns 1 row

  • hello returns 2 lines

  • "hello developer" returns 2 lines

  • "xyz" returns 2 rows

+2
sql sql-server tsql sql-server-2005 sql-server-2000
source share
2 answers

Since you cannot use the table parameter (and not on SQL Server 2008), try passing it to the CSV sting and store it in a row break.

There are many ways to split a string in SQL Server. This article discusses PRO and CON for almost every method:

"Arrays and Lists in SQL Server 2005 and Beyond when Table Parameters Don't Shorten" Erland Sommarskog

You need to create a split function. Here's how to use the split function:

 SELECT * FROM YourTable y INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split strings in TSQL , but there are many ways to split strings in SQL Server, see the previous link that explains the PRO and CON of each of them.

For the Numbers Table method to work, you need to perform this setting of a single time table that will create a Numbers table that contains rows from 1 to 10000:

 SELECT TOP 10000 IDENTITY(int,1,1) AS Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is configured, create this split function:

 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 TABLE AS RETURN ( ---------------- --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 dt INNER JOIN Numbers n ON n.Number < LEN(dt.List2) WHERE SUBSTRING(List2, number, 1) = @SplitOn ) dt2 WHERE ListValue IS NOT NULL AND ListValue!='' ); GO

Now you can easily split the space-delimited string into a table and join or use it, but you need it. These codes are based on the last OP editing question:

 CREATE TABLE YourTable (PK int, col1 varchar(20), col2 varchar(20), col3 varchar(20)) --data from question INSERT INTO YourTable VALUES (1,'hello xyz','abc is my last name','and i''ma developer') INSERT INTO YourTable VALUES (2,'hello xyz',null,'and i''ma developer') CREATE PROCEDURE YourProcedure ( @keywords varchar(1000) ) AS SELECT @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.PK GO --from question EXEC YourProcedure 'xyz developer'-- returns 2 rows EXEC YourProcedure 'xyz abc'-- returns 1 row EXEC YourProcedure 'abc developer'-- returns 1 row EXEC YourProcedure 'hello'-- returns 2 rows EXEC YourProcedure 'hello developer'-- returns 2 rows EXEC YourProcedure 'xyz'-- returns 2 rows

OUTPUT:

 KeyWords PK col1 col2 col3 -------------- ----- ---------- -------------------- -------------------- xyz developer 1 hello xyz abc is my last name and i'm a developer xyz 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 developer hello 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 developer hello 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 developer xyz 2 hello xyz NULL and i'm a developer (2 row(s) affected)
+2
source share

You can try something like:

 select firstname, lastname from @test t1 inner join persondata t on t.firstname like '%' + t1.x + '%' or t.lastname like '%' + t1.x + '%' group by firstname, lastname having count(distinct x) = (select count(*) from @test)

where @test is the table with the results of your separation. If you have many columns in persondata, you can simply return the identifier from this query and use it as a subquery for the one that actually returns data, so you don't need to group as many columns.

Edit: You can also use the cursor and another temp table / table variable, but I have an allergic reaction to cursors in SP.

+1
source share

More articles:


All Articles