March 19, 2013 at 5:17 am
Hi all. I have written a script but in this I am not getting value in a column which is populating through CASE statement. Its only returning NULL
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
WHILE CHARINDEX(' ',@vouchertype,1)>0
BEGIN
SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1)));
SET @retval+=LEFT(@vouchertype,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
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 19, 2013 at 6:00 am
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?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 19, 2013 at 6:04 am
Jason-299789 (3/19/2013)
I think you will find that this is the problemSET @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?
I used that Print statement IF EXISTS and BEGIn to check the values of a variable but if I dont write print statement then this script keeps on executing... :unsure:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 19, 2013 at 6:16 am
I think SET @vouchertype = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype)-CHARINDEX(' ',@vouchertype,1))); is actually changing the value for vouchertype.
You may want to include an ELSE as well within your CASE statement.
March 19, 2013 at 6:16 am
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.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 19, 2013 at 6:29 am
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/
March 19, 2013 at 6:55 am
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.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 19, 2013 at 7:02 am
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.
Thanks a lot Jason 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply