代码之家 › 专栏 › 技术社区 › User987

存储过程返回结果,甚至认为它不应该返回

logical-operators stored-procedures sql-server-2008 sql-server sql

1

User987 · 技术社区 · 7 年前

我有一个如下所示的存储过程:

ALTER PROCDURE [dbo].[zsp_selectallupceans_listProduction]
    (@UPCList NVARCHAR(4000),
     @EANList NVARCHAR(4000),
     @Type TINYINT)
AS
    SELECT  
        dd.UPC, dd.EAN, dd.EBAYID AS ItemID
    FROM
        ThirdPartyData AS dd 
    WHERE 
        EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@UPCList,',') S1 
                WHERE dd.UPC = S1.val) 
        OR EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@EANList,',') S2 
                   WHERE dd.EAN = S2.val) 
        AND dd.Type = @Type

@UPCList='709127309019',
@EanList='0709127309019',
@Type=4

“SplitStringProduction”函数如下所示:

ALTER FUNCTION [dbo].[SplitStringProduction]
    (@string NVARCHAR(MAX),
     @delimiter NVARCHAR(5)) 
RETURNS @t TABLE
           (
                val NVARCHAR(500)
           )
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<root><r>' + replace(@string,@delimiter,'</r><r>') + '</r></root>'

    INSERT INTO @t(val)
       SELECT
           r.value('.','varchar(500)') AS item
       FROM
           @xml.nodes('//root/r') as records(r)

    RETURN
END

select * 
from thirdpartydata dd
where dd.UPC = '709127309019' -- note this is one of the parameters passed to the stored procedure...

我将只得到一个带有列的结果 Type 设置为“1”。。。。

exec  zsp_selectallupceans_listProduction '709127309019','0709127309019',4

我到底做错了什么,我似乎不明白??

1 回复 | 直到 7 年前

1

2

Salman Arshad 7 年前

你需要把这封信附上 OR 括号内的条件:

WHERE (
      EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@UPCList,',') S1 WHERE dd.UPC=S1.val) 
   OR EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@EANList,',') S2 WHERE dd.EAN=S2.val) 
) AND dd.Type = @Type

如果没有它们,您的查询如下所示:

WHERE EXISTS (...) 
   OR (EXISTS (...) AND dd.Type = @Type)