Click here to Skip to main content
15,747,637 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

From Products
inner join Cities on Products.P_ID = Cities.P_ID
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%
Updated 3-Jul-22 12:20pm

1 solution

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'
    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%'
Share this answer
Hamza M. S. Abazeed 4-Jul-22 9:01am    
Thanks Dave, Actually I didn't know that loops can't be performed in the where clause, I guess the best solution is to filter the conditions that need a loop in DAL before inserting them into the list.
many thanks.
Dave Kreskowiak 4-Jul-22 11:15am    
It appears you changed the original statement. You also cannot have an IF statement in the WHERE clause either.
Hamza M. S. Abazeed 4-Jul-22 14:30pm    
I figured that out from your answer, I did the loops and the conditional statement using c# after retrieving the data.
Dave Kreskowiak 4-Jul-22 16:11pm    
While that works, it doesn't scale. You're pulling a ton of data from the database when you don't have to. Let the database do the filtering.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900