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

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%'
)

Search results in SQL server using loop in where clause

1 solution

Solution 1

Add your solution here

Preview 0

Search results in SQL server using loop in where clause

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us