Search results in SQL server using loop in where clause

Question

0.00/5 (No votes)

See more:

, +

Good afternoon,

I'm trying to complete a small project using SQL Server, but I'm having some troubles ;/

I'm trying to Serch in SQL Server table, where I have a statement (one word or many words)

I have a table two tables:
- Products (P_ID int, P_Title nvarchar(max), P_Activeflag bit P_Dilivery_Flag bit .... )
- Cities (P_ID int (product id), C_Title .... )

And I bass 3 parameters to this Stored Procedure:
@_Cities nvarchar: a text of cities separated by ',' (Berlin, Frankfurt, Kassel, .. )

@_Dilivery bit: if true I want to search only in products with P_Dilivery_Flag = 'true' and have at least one of the cities listed in Cities Table with its ID

I tried for that a loop in the WHERE clause, but I had always syntax errors.

@_SeachText nvarchar: I have a wish - to get (if exist) the products with the exact text (P_Title = @_SearchText) after that get all products that contain a word that matches a word in @_SearchText
if it is not possible, just the products that have a word that matches a word in the parameter @_SearchText.

I have many tries but none is completed or can explain what I was trying to achieve.

thanks in advance.

What I have tried:

select
Products.P_ID,
Products.P_Title,
Products.P_Activeflag
Products.P_Dilivery_Flag
From Products
inner join Cities on Products.P_ID = Cities.P_ID
WHERE
Products.P_Activeflag = 'true'
-- AND
-- if @_Dilivery = 'true' search in cities where Cities.C_Title = one of the cities in @_Cities

-- AND
-- Loop in each word in @_SearchText as @_OneWord
-- Products.P_Title LIKE %@_OneWord%

Posted 3-Jul-22 10:50am

Hamza M. S. Abazeed

Updated 3-Jul-22 12:20pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2022-07-03T12:20:00

You cannot use a LOOP expression in a WHERE clause. The WHERE clause expects to see only conditional expressions.

You can build the SQL statement in a variable, use the LOOP clause to build up a WHERE clause with multiple OR expressions, then use EXEC to execute the built SQL Statement. The resulting statement would look something like this:

SELECT Products.P_ID
    , Products.P_Title
    , Products.P_ActiveFlag
    , Products.Delivery_Flag
FROM Products
INNER JOIN Cities on Products.P_ID = Cities.P_ID
WHERE Products.P_ActiveFlag = 'true'
AND (
    Products.P_Title LIKE '%SearchTerm0%'
    OR Products.P_Title LIKE '%SearchTerm1%'
    OR Products.P_Title LIKE '%SearchTerm2%'
    OR Products.P_Title LIKE '%SearchTerm3%'
    OR Products.P_Title LIKE '%SearchTerm4%'
)