CREATE PROCEDURE dbo.spoGetItemProfit
@ItemNo varchar(30),
@ItemPrice money
as
SELECT
TOP 1
I.ItemNo,
Price =@ItemPrice,
General=P.ProfitGeneral,
Special=P.ProfitSpecial,
T.ChildID
FROM
bItem I,
bTreeNode T,
bItemProfit P
WHERE
I.ChildID = T.ChildID
AND I.ItemNo = P.ProfitID
AND I.ItemNo = @ItemNo
AND (@ItemPrice between P.PriceMin and PriceMax)
AND InheritType ='byself'
IF @@ROWCOUNT <= 0
begin
SELECT
TOP 1
I.ItemNo,
Price =@ItemPrice,
General=P.ProfitGeneral,
Special=P.ProfitSpecial,
P.ProfitID
FROM
bItem I,
bTreeNode T,
bItemProfit P
WHERE
I.ChildID = T.ChildID
AND T.ParentID = P.ProfitID
AND I.ItemNo = @ItemNo
AND (@ItemPrice between P.PriceMin and PriceMax)
AND InheritType ='father'
end
GO此语句可以正常通过,只是 因为在 IF @@ROWCOUNT <= 0 的情况下,会输出两个记录集,请问如何优化后,只输出一个记录集?!
@ItemNo varchar(30),
@ItemPrice money
as
SELECT
TOP 1
I.ItemNo,
Price =@ItemPrice,
General=P.ProfitGeneral,
Special=P.ProfitSpecial,
T.ChildID
FROM
bItem I,
bTreeNode T,
bItemProfit P
WHERE
I.ChildID = T.ChildID
AND I.ItemNo = P.ProfitID
AND I.ItemNo = @ItemNo
AND (@ItemPrice between P.PriceMin and PriceMax)
AND InheritType ='byself'
IF @@ROWCOUNT <= 0
begin
SELECT
TOP 1
I.ItemNo,
Price =@ItemPrice,
General=P.ProfitGeneral,
Special=P.ProfitSpecial,
P.ProfitID
FROM
bItem I,
bTreeNode T,
bItemProfit P
WHERE
I.ChildID = T.ChildID
AND T.ParentID = P.ProfitID
AND I.ItemNo = @ItemNo
AND (@ItemPrice between P.PriceMin and PriceMax)
AND InheritType ='father'
end
GO此语句可以正常通过,只是 因为在 IF @@ROWCOUNT <= 0 的情况下,会输出两个记录集,请问如何优化后,只输出一个记录集?!
begin
若存在结果集
执行1
end
else
begin
若不存在结果集
执行2
end
@ItemNo varchar(30),
@ItemPrice money
as
BEGIN if exists(select 1 from FROM
bItem I,
bTreeNode T,
bItemProfit P
WHERE
I.ChildID = T.ChildID
AND I.ItemNo = P.ProfitID
AND I.ItemNo = @ItemNo
AND (@ItemPrice between P.PriceMin and PriceMax)
AND InheritType ='byself')
BEGIN
SELECT
TOP 1
I.ItemNo,
Price =@ItemPrice,
General=P.ProfitGeneral,
Special=P.ProfitSpecial,
T.ChildID
FROM
bItem I,
bTreeNode T,
bItemProfit P
WHERE
I.ChildID = T.ChildID
AND I.ItemNo = P.ProfitID
AND I.ItemNo = @ItemNo
AND (@ItemPrice between P.PriceMin and PriceMax)
AND InheritType ='byself'
END
ELSE
BEGIN
SELECT
TOP 1
I.ItemNo,
Price =@ItemPrice,
General=P.ProfitGeneral,
Special=P.ProfitSpecial,
P.ProfitID
FROM
bItem I,
bTreeNode T,
bItemProfit P
WHERE
I.ChildID = T.ChildID
AND T.ParentID = P.ProfitID
AND I.ItemNo = @ItemNo
AND (@ItemPrice between P.PriceMin and PriceMax)
AND InheritType ='father'
END
END
GO