通过使用Like运算符创建内部联接

我正在使用SQL Server DBMS在java fx中开发一般商店管理 . 我必须将数据存储在表中,以便我必须为搜索数据放置一个文本字段,我必须在数据库中搜索SQL:

CREATE PROC Spgetproductlistwithsearch @value NVARCHAR(max) 
AS 
  BEGIN 
      SELECT p.product_id, 
             p.NAME, 
             pt.type, 
             p.pur_price, 
             p.sale_price, 
             p.stock, 
             s.NAME, 
             s.contact_no, 
             s.email 
      FROM   ((product AS p 
               INNER JOIN producttype AS pt 
                       ON p.type_id = pt.type_id) 
              INNER JOIN supplier AS s 
                      ON p.supplier_id = s.supplier_id) 
      WHERE  p.NAME LIKE @value 
              OR s.NAME LIKE @value 
              OR pt.type LIKE @value 
              OR p.pur_price LIKE @value 
              OR p.sale_price LIKE @value 
              OR p.stock LIKE @value 
              OR p.product_id LIKE @value 
              OR s.contact_no LIKE @value 
              OR s.email LIKE @value 
  END; 

EXEC Spgetproductlist

但它没有给我预期的值,它给了我两张桌子,但我需要加入所有这些表格

回答(1)

2 years ago

试试这个 :

您可以查看以下链接以获取更多详细信息:Usinig like operator with stored procedures parameters

CREATE PROC spGetProductListWithSearch 
        @value nvarchar(MAX)
    AS
    BEGIN
        SELECT
            p.Product_id
           ,p.Name
           ,pt.Type
           ,p.Pur_Price
           ,p.Sale_Price
           ,p.Stock
           ,s.Name
           ,s.Contact_No
           ,s.Email
        FROM ((Product AS p
        INNER JOIN ProductType AS pt
            ON p.Type_Id = pt.Type_Id)
        INNER JOIN Supplier AS s
            ON p.Supplier_Id = s.Supplier_Id)
        WHERE p.Name LIKE '%'+@value+'%'
        OR s.Name LIKE '%'+@value+'%'
        OR pt.Type LIKE '%'+@value+'%'
        OR p.Pur_Price LIKE '%'+@value+'%'
        OR p.Sale_Price LIKE '%'+@value+'%'
        OR p.Stock LIKE '%'+@value+'%'
        OR p.Product_id LIKE '%'+@value+'%'
        OR s.Contact_No LIKE '%'+@value+'%'
        OR s.Email LIKE '%'+@value+'%'
    END
    GO
    EXEC spGetProductListWithSearch
        @value = 'test'