Jason-299789 (3/19/2013)

---

I think you will find that this is the problem

SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));

As you seem to be taking Right N characters, in @vouchertype, and over Writing it so If @VoucherType='Value Discount' you are then changing @vouchertype to 'Discount' so @VoucherType != 'Value Discount'

You also have a Print Statement between the IF EXISTS and the BEGIN, I believe that this will then cause everything in the Next BEGIN/END batch to execute regardless of the outcome of the EXISTS.

Eg

IF EXISTS(Select 1)
PRINT 'TRUE'
BEGIN
Print 'FALSE'
END 

Both true and False are printed. Is this intended behaviour?

Jason-299789 (3/19/2013)

---

I probably got hold of the wrong end of the stick.

But I think you will find the problem is the SET @Vouchertype, if you add a Print @VoucherType after the set

Declare @vouchertype varchar(20) = 'Value Discount',
@denomination int,
@userid int,
@transactionID varchar(20),
@voucherstatus int,
@quantity int = 10,
@count int =1,
@max int,
@vouchertype1 varchar(20),
@amountvalue int
declare @retval varchar(20)
SET @vouchertype=RTRIM(LTRIM(@vouchertype));
SET @retval=LEFT(@vouchertype,1);
SET @amountvalue = 1000

SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));
print @vouchertype

this Returns 'Discount', which makes the case invalid as 'Discount' doenst equal any of the cases.

I'm still not sure about the purpose of the IF EXISTS, I would have thought the original logic was that you want to check if the vouchertype exists, and if it does then add a new record and increment a sequence table.

but in its current form it will always add a row regardless of if the voucher type exists.

Jason-299789 (3/19/2013)

---

The simplest way is to do this as you now have the @VoucherType1 set is to dot his

CASE @vouchertype1 
    WHEN 'Discount' THEN @amountvalue
    ELSE 0
END 

this way when you have a vouchertype that ends with 'Discount' you apply the amount otherwise you simply insert a 0.