How to use if else in select statement in sql server 2008

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

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

SQL

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

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

Posted 8-Jul-13 23:47pm

shivani 2013

Add a Solution

Comments

Adarsh chauhan 9-Jul-13 5:58am

can you Provide your table structure, and also explain what you want to achieve?
can't say about others but i am having little doubt in your logic used.

well in your case you have missed 'end' keyword..

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

2 solutions

Solution 2

E.G.

SQL

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 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

Posted 9-Jul-13 0:00am

Nirav Prabtani

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ArunRajendra · Accepted Answer · 2013-07-08T23:58:00

Solution 1

Try with this code.

SQL

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

Posted 8-Jul-13 23:58pm

ArunRajendra

Comments

shivani 2013 9-Jul-13 6:10am

thanks it worked, tried everything but not this neeways thanks once again