question

MuriloJunqueira-9599 avatar image
0 Votes"
MuriloJunqueira-9599 asked NaomiNNN commented

Help in creating procedure with filters

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-general
· 15
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello guys, I think I expressed myself badly, I don't have the "word" field in the SALES table, but in the PRODUCTS AND BYPRODUCTS tables.
and I need to bring the SALES records that have some word in the word field of the PRODUCTS table or the SUBPRODUCTS table.
using my friend @TomPhillips-1744's example I did the following, it worked, I would like to know if it is the best option in terms of performance.


 declare @words nvarchar(MAX);
 set @words = 'tubo,fenda,chave,vidro';
 select * from sales l
 cross apply string_split(@words, ',') words
 where l.id in (select idsale from products where word_name like concat('%', words.[value], '%') or word_description like concat('%', words.[value], '%'))
 or  l.id in (select idsale from subproduct where word_name like concat('%', words.[value], '%') or word_description like concat('%', words.[value], '%'))


1 Vote 1 ·
ErlandSommarskog avatar image ErlandSommarskog MuriloJunqueira-9599 ·

From a performance point of view, the best is not to store data as comma-separated lists at all, but create a subtable for the purpose!

0 Votes 0 ·
TomPhillips-1744 avatar image TomPhillips-1744 MuriloJunqueira-9599 ·

I would not use sub-selects Those are going to perform poorly.
In any case, LIKE will not use indexes.

 declare @words nvarchar(MAX);
  set @words = 'tubo,fenda,chave,vidro';
  select * from sales l
     LEFT OUTER JOIN  products p
         ON p.idsale = l.id
     LEFT OUTER JOIN subproduct sp
         ON sp.idsale = l.id
      cross apply string_split(@words, ',') words
  where (p.word_name like concat('%', words.[value], '%') or p.word_description like concat('%', words.[value], '%')
     or  sp.word_name like concat('%', words.[value], '%') or sp.word_description like concat('%', words.[value], '%'))
0 Votes 0 ·

There is a problem, because it returned many repeated records, it was running for more than 1 minute until I canceled the operation.


0 Votes 0 ·
Show more comments
NaomiNNN avatar image NaomiNNN MuriloJunqueira-9599 ·

Using my idea of splitting the words first into a separate table variable, I would do:

 ;with cteProducts as (select p.idSale, w.Word as MatchProduct from Products p inner join @Words w on p.Word = w.Word -- if you want to use exact match, or alternatively where p.word like '%'+w.Word + '%'),
 cteSubProducts as (select s.idSale, w.Word as MatchSubProduct from SubProduct s inner join ....)
    
 select s.*, ISNULL(p.MatchProduct, s.MatchSubProduct) as [Product] from Sales s 
 left join cteProducts p on s.id = p.idSale
 left join cteSubProducts sp on s.id = sp.idSale
 where s.SaleDate between @StartDate and @EndDate and ISNULL(p.MatchProduct, s.MatchSubProduct) IS NOT NULL
0 Votes 0 ·

excuse my ignorance, but I could not complete this complete example, "incorrect syntax", I believe it is in the separation of the blocks.

 declare @Products nvarchar(MAX);
 set @Products  = 'tubo,fenda,chave,vidro';
    
 declare @Words table (word varchar(40))
  insert into @Words (word)
  select rtrim(ltrim(value)) from string_split(@Products, ',') where value <> '';
    
  ;with cteProducts as (select p.idSale, p.word as MatchProduct from Products p inner join @words w on p.word = w.Word)
  ***cteSubProducts*** as (select s.idSale, w.Word as MatchSubProduct from SubProduct s inner join @words w on s.word = w.Word)
        
  select s.*, ISNULL(p.MatchProduct, s.MatchSubProduct) as [Product] from Sales s 
  left join cteProducts p on s.id = p.idSale
  left join cteSubProducts sp on s.id = sp.idSale
  where s.SaleDate between @StartDate and @EndDate and ISNULL(p.MatchProduct, s.MatchSubProduct) IS NOT NULL






0 Votes 0 ·

excuse my ignorance, but I could not complete this complete example, "incorrect syntax", I believe it is in the separation of the blocks.

 declare @Products nvarchar(MAX);
 set @Products  = 'tubo,fenda,chave,vidro';
    
 declare @Words table (word varchar(40))
  insert into @Words (word)
  select rtrim(ltrim(value)) from string_split(@Products, ',') where value <> '';
    
  ;with cteProducts as (select p.idSale, p.word as MatchProduct from Products p inner join @words w on p.word = w.Word)
  cteSubProducts as (select s.idSale, w.Word as MatchSubProduct from SubProduct s inner join @words w on s.word = w.Word)
        
  select s.*, ISNULL(p.MatchProduct, s.MatchSubProduct) as [Product] from Sales s 
  left join cteProducts p on s.id = p.idSale
  left join cteSubProducts sp on s.id = sp.idSale
  where s.SaleDate between @StartDate and @EndDate and ISNULL(p.MatchProduct, s.MatchSubProduct) IS NOT NULL
0 Votes 0 ·
Show more comments

in the PRODUCTS and BYOPRODUCTS tables I have the IDSALES field, which refers to the SALES table!

0 Votes 0 ·

Consider this concept too:

 declare @words varchar(max) = 'RICE, Noodles, SWEET POTATOES, STEAK, ROASTED PEANUTS'
    
 declare @sql varchar(max) = concat( '
 select *
 from Sales 
 where freetext(words, ''', @words, ''')')
    
 exec (@sql)

Create a Full Text Catalog too, that includes the SALES table and words column (using Management Studio).

If it gives good results, it can be improved, and also extended for your date parameters and other needs. Consider CONTAINSTABLE too.


0 Votes 0 ·

When it comes to performance, this is absolutely a better option than the other queries, but it also come with more complexity.

Full-text is what you need when you have columns that are truly free text like comments, descriptions etc.

But comma-separated lists should be not be at all. They should be subtables.

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered MuriloJunqueira-9599 commented

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 ;
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you all for the help and the stone path, the final result was as below, it worked perfectly, and it brought all the records in 00:00:01 s
Thanks @NaomiNNN

declare @Products nvarchar(MAX);
set @Products = 'tubo,fenda,chave,vidro';

 declare @Words table (word varchar(40))
  insert into @Words (word)
  select rtrim(ltrim(value)) from string_split(@Products, ',') where value <> '';
    
  with cteProducts as (
      select p.idSale, w.word as MatchProduct from itens p inner join @words w on p.nome like '%' + w.Word + '%' or p.descricao like '%' + w.Word + '%'
  ),
     
  cteSubProducts as (
     select d.idSale, w.word as MatchSubProduct from itenslote d inner join @words w on d.nome like '%' + w.Word + '%' or d.descricao like '%' + w.Word + '%'
  )
        
  select distinct s.*, ISNULL(p.MatchProduct, sp.MatchSubProduct) as [Product] from sales s 
  left join cteProducts p on s.id = p.idSale
  left join cteSubProducts sp on s.id = sp.idSale
 where s.datesale between cast('2022-05-01' as date) and cast('2022-05-30' as date) and 
 ISNULL(p.MatchProduct, sp.MatchSubProduct) IS NOT NULL
0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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]),'%')
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.