Help in creating procedure with filters

Murilo Junqueira 26 Reputation points
2022-05-27T10:14:26.077+00:00

Hello guys, I need to create a procedure that makes some filters and returns me records, but my knowledge in Sql Server is limited to simple queries, I have the following tables:

SALES
PRODUCTS
BYPRODUCTS

need to select SALES within a period that contains in the PRODUCTS or BYPRODUCTS table the keywords passed by the user, which can be one or several, ex:

startDate = 05/22/2022
EndDate = 05/26/2022
words = 'RICE, Noodles, SWEET POTATOES, STEAK, ROASTED PEANUTS'

if it was just one word it could do:

SELECT * FROM SALES WHERE SALESDATE BETWEEN startDate and EndDate and words like '%word%'

but how can I convert the variable "words" into a string array and loop "or words like array_words[i]" in this procedure? not sure if this would be the approach to the desired result.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points
    2022-05-27T13:43:18.41+00:00

    What is your SQL Server version? In SQL Server 2016 and up try this solution:

    create or alter procedure GetSalesInfo
    
    (@startDate date = '05/22/2022',  @EndDate date = '05/26/2022',
    @Products varchar(max) = 'RICE, Noodles, SWEET POTATOES, STEAK, ROASTED PEANUTS')
    
    AS 
    
    begin
    
    declare @Words table (word varchar(40))
    insert into @Words (word)
    select rtrim(ltrim(value)) from string_split(@Products, ',') where value <> '';
    
    SELECT s.* FROM SALES s
    inner join @Words w on s.Word like '%' + w.word+'%'
    
     WHERE s.SALESDATE BETWEEN @startDate and @EndDate ;
    

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2022-05-27T12:35:42.357+00:00

    You need to split the list of words into table format. This short article on my web site discusses how to do that: https://www.sommarskog.se/arrays-in-sql.html.

    1 person found this answer helpful.
    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2022-05-27T14:05:10.153+00:00

    You don't "loop" in SQL Server.

    You can query it directly by using this:

    SELECT * 
    FROM SALES 
        CROSS APPLY STRING_SPLIT(@words,',') words
    WHERE SALESDATE BETWEEN @startDate and @EndDate 
        and word like CONCAT('%',TRIM(words.[value]),'%')
    
    0 comments No comments