Блог

я создал эту сохраненную функцию для построения расширенного процесса поиска, но я заметил, что она не возвращает никаких результатов, хотя я пробую код без использования сохраненных результатов, которые он возвращает правильно

я думаю, что что-то не так с параметром SearchWord

 DELIMITER //
CREATE PROCEDURE AdvancedSearch(IN SearchWord VARCHAR(255))
  BEGIN
    SELECT * FROM (

                    SELECT CustomerId AS id,CustomerCompany  AS name ,null AS descrip,null AS FileName
                    FROM tbcustomers
                      LEFT JOIN tbfiles ON tbcustomers.CustomerId  = tbfiles.CatId AND tbfiles.ModuleName = "customers"
                    WHERE tbcustomers.CustomerCompany LIKE  CONCAT('%', @SearchWord , '%')

                    UNION
                    SELECT DeviceId AS id,DeviceName AS name,DeviceDesc AS descrip,FileName
                    FROM tbdevices
                      LEFT JOIN tbfiles ON tbdevices.DeviceId  = tbfiles.CatId AND tbfiles.ModuleName = "devices"
                    WHERE tbdevices.DeviceName  LIKE  CONCAT('%', @SearchWord , '%')
                          OR tbdevices.DeviceDesc  LIKE CONCAT('%', @SearchWord , '%')
                          OR tbdevices.DeviceProperties  LIKE CONCAT('%', @SearchWord , '%')

                    UNION
                    SELECT HostingId AS id,HostingName  AS name,HostingType AS descrip,FileName
                    FROM tbhosting
                      LEFT JOIN tbfiles ON tbhosting.HostingId  = tbfiles.CatId AND tbfiles.ModuleName = "hosting"
                    WHERE tbhosting.HostingName  LIKE CONCAT('%', @SearchWord , '%')
                          OR    tbhosting.HostingType  LIKE CONCAT('%', @SearchWord , '%')
                          OR   tbhosting.HostingSpace  LIKE CONCAT('%', @SearchWord , '%')
                          OR   tbhosting.HostingRam  LIKE CONCAT('%', @SearchWord , '%')
                          OR  tbhosting.HostingUsage  LIKE CONCAT('%', @SearchWord , '%')
                          OR tbhosting.HostingUsagebases  LIKE CONCAT('%', @SearchWord , '%')

                    UNION
                    SELECT PageId AS id,PageTitle  AS name,PageDesc AS descrip,FileName
                    FROM tbpages
                      LEFT JOIN tbfiles ON tbpages.PageId  = tbfiles.CatId AND tbfiles.ModuleName = "pages"
                    WHERE tbpages.PageTitle  LIKE CONCAT('%', @SearchWord , '%')
                          OR    tbpages.PageDesc  LIKE CONCAT('%', @SearchWord , '%')
                          OR   tbpages.PageSubject  LIKE CONCAT('%', @SearchWord , '%')


                    UNION
                    SELECT ProductId AS id,ProductName  AS name,ProductDesc AS descrip,FileName
                    FROM tbproducts
                      LEFT JOIN tbfiles ON tbproducts.ProductId  = tbfiles.CatId AND tbfiles.ModuleName = "products"
                    WHERE tbproducts.ProductName  LIKE CONCAT('%', @SearchWord , '%')
                          OR    tbproducts.ProductTitle  LIKE CONCAT('%', @SearchWord , '%')
                          OR   tbproducts.ProductDesc  LIKE CONCAT('%', @SearchWord , '%')
                          OR   tbproducts.ProductProperties  LIKE CONCAT('%', @SearchWord , '%')
                          OR   tbproducts.ProductPrice  LIKE CONCAT('%', @SearchWord , '%')
                          OR   tbproducts.ProductNumVersion  LIKE CONCAT('%', @SearchWord , '%')

                    UNION
                    SELECT ProjectId AS id,ProjectName  AS name,ProjectDesc AS descrip,FileName
                    FROM tbprojects
                      LEFT JOIN tbfiles ON tbprojects.ProjectId  = tbfiles.CatId AND tbfiles.ModuleName = "projects"
                    WHERE tbprojects.ProjectName  LIKE CONCAT('%', @SearchWord , '%')
                          OR tbprojects.ProjectDesc  LIKE CONCAT('%', @SearchWord , '%')
                  ) AS v
    WHERE v.name != ''
    GROUP BY v.id;
  END //
DELIMITER ;
  

Имя параметра — SearchWord, но вы использовали его как @SearchWord в теле процедуры. Удалите символ @, и он будет работать