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-2 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.
Thanks Jason for pointing out the problem....I have changes the script and it working now..
Declare @vouchertype varchar(20) = 'Value Discount',
@denomination int,
@userid int,
@transactionID varchar(20),
@voucherstatus int,
@quantity int = 10,
@count int =1,
@max-2 int,
@vouchertype1 varchar(20),
@amountvalue int
declare @retval varchar(20)
--Declare
SET @vouchertype1=RTRIM(LTRIM(@vouchertype));
SET @retval=LEFT(@vouchertype1,1);
SET @amountvalue = 5000
WHILE CHARINDEX(' ',@vouchertype1,1)>0
BEGIN
SET @vouchertype1 = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));
SET @retval+=LEFT(@vouchertype1,1);
END
WHILE @count <= @quantity
BEGIN
SET @max-2 = (select vouchervaluebased from gv_maxvouchervalue)
SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue)
If EXIsts (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--print @retval
BEGIN
INSERT INTO GV_Voucher
VALUES
(
1,
@retval + Replace( Str( @max-2+1, 7), ' ', '0') ,
20,
DATEADD(MM,6,GETDATE()),
GETDATE(),
GETDATE(),
1,
1,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0'),
@quantity,
CASE WHEN @vouchertype = 'Value Based' THEN 0
WHEN @vouchertype = 'Value Discount' THEN @amountvalue
WHEN @vouchertype = 'Percentage Discount' THEN @amountvalue
END
)
SET @count = @count + 1
Update gv_maxvouchervalue
SET vouchervaluebased = @max-2 + 1
END
--print @amountvalue
END
UPDATE gv_maxvouchervalue
SET vouchertransactionID = @transactionID + 1
But I have one more question I dont the static values in CASE statement. I only want to add amountvalue for voucher types other than 'value Based'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/