如何在sql server 2008中的select语句中使用if else [英] How to use if else in select statement in sql server 2008

问题描述

我需要在select语句中使用if else语句..我创建了SP并尝试了以下

I need to use if else statement in select statement ..I created SP and tried following

ALTER PROCEDURE [PR].[SPJ_PurchaseOrderDShipmentD]
--[PR].[SPJ_PurchaseOrderDShipmentD] 'FY12/PO/172'
    -- Add the parameters for the stored procedure here
@ParentDocCode varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
SELECT distinct D.DocCode, D.FKFYCode, D.DocPrefix, D.DocNo, D.DocDate, D.FkItemCode,
D.FkItemName,D.FkItemName,
ISNULL((select InventoryUOMCode from MM.ITMIV where FkItemCode=D.FkItemCode),'0') as UOMCode,
ISNULL((select InventoryUOM from MM.ITMIV where FkItemCode=D.FkItemCode),'NA') as UOMName,

CAST(D.qty + (ISNULL((sELECT sUM(qty) FROM PR.PMDTL WHERE FkItemCode=D.FkItemCode AND ParentDocCode=D.DOCCODE),0)) AS Decimal(18,2)) as TotalPOQty,
CAST(ISNULL((sELECT sUM(QTY) FROM PR.PSDTL WHERE FkItemCode=D.FkItemCode AND ParentDocCode=D.DocCode ),0) AS Decimal(18,2)) PreviousIssuedQty,

CAST( Qty + (ISNULL((sELECT sUM(qty) FROM PR.PMDTL WHERE FkItemCode=D.FkItemCode AND ParentDocCode=D.DocCode),0)) - (
ISNULL((sELECT sUM(QTY) FROM PR.PSDTL WHERE FkItemCode=D.FkItemCode AND ParentDocCode=D.DocCode ) ,0)) AS Decimal(18,2))
 as Qty,0 as NextBalanceQty ,

case when P.Factor1=' '
then 0
else P.Factor1 as BrookerageAmt 

from pr.PODTL P where FkItemCode=D.FkItemCode and DocCode=@ParentDocCode)


--ISNULL((select Factor1 from PR.PODTL where FkItemCode=D.FkItemCode and DocCode=@ParentDocCode),0.00 ) as BrokerageAmt

 FROM
PR.POHDR H,PR.PODAC D
where D.DocCode=@ParentDocCode and d.DocCode = h.DocCode AND
Qty >0
 --(ISNULL((sELECT sUM(qty) FROM PR.PMDTL WHERE FkItemCode=D.FkItemCode AND ParentDocCode=D.DocCode),0)) -
 --(ISNULL((SELECT sUM(QTY) FROM PR.PSDTL WHERE FkItemCode=D.FkItemCode AND ParentDocCode=D.DocCode ),0) ) )> 0
and H.Status='Y' and D.Status='Y'


END

但是收到错误请帮帮我

任何帮助明白

问候，

Shivani

but getting an error pls help me out
any help appreciable
regards,
Shivani

推荐答案

尝试使用此代码。

Try with this code.

(select case when P.Factor1=' ' then 0
else P.Factor1
 end
from pr.PODTL P where FkItemCode=D.FkItemCode and DocCode=@ParentDocCode) as BrookerageAmt

EG

E.G.

DECLARE @compareprice money, @cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @compareprice OUT, 
    @cost OUTPUT
IF @cost <= @compareprice 
BEGIN
    PRINT 'These products can be purchased for less than

' + RTRIM（CAST（ @ compareprice AS varchar （ 20 ）））+ ' 。'
END
ELSE
PRINT ' 此类别中所有产品的价格超过

'+RTRIM(CAST(@compareprice AS varchar(20)))+'.' END ELSE PRINT 'The prices for all products in this category exceed

这篇关于如何在sql server 2008中的select语句中使用if else的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！