CODEWITHSUNDEEP
mysqlsql
user1080247
user1080247

Reputation: 1166

why this stored does'nt return results

i have created this stored function to build advanced search process but i noticed it hasn't return any results although i try code without using stored any it return results correctly

i think something wrong with the parameter 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 ;

Upvotes: 0

Views: 39

Answers (1)

Madhivanan
Madhivanan

Reputation: 13700

The parameter name is SearchWord But you used it as @SearchWord in the body of the procedure. Remove @symbol and it will work

Upvotes: 2

Related Questions