Jeff Moden (3/17/2013)
kapil_kk (3/16/2013)
I have solved it..... forget to initialize the IsBookelet variable value ;-):w00t:I tried your code and even with the correction you posted, it does nothing because many of the other variables haven't been initialized.
If you'd like, please post your final code and we'll show you how to do this without the overhead of the loop.
Hi Jeff,
Here is the code that you were asking:
Declare @vouchertype varchar(20) = 'Value Discount',
@denomination int,
@userid int,
@voucherstatus int,
@transactionID varchar(20),
@quantity int = 1,
@count int =1,
@innercount int =1,
@leaf int =10,
@max-2 int
WHILE @count <= @quantity
BEGIN
WHILE @innercount <= @leaf
BEGIN
SET @max-2 = (select vouchervaluebased from gv_maxvouchervalue)
SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue)
if @vouchertype = (select VoucherType from GV_VoucherType where VoucherType = 'Value Based')
BEGIN
INSERT INTO GV_Booklet
VALUES
(
@leaf,
500,
1,
'VB' + Replace( Str( @max-2+1, 7), ' ', '0') ,
@quantity,
GETDATE(),
GETDATE(),
DATEADD(MM,6,GETDATE()),
1,
1,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0')
)
SET @innercount = @innercount + 1
UPDATE gv_maxvouchervalue
SET vouchervaluebased = @max-2 + 1
END
ELSE if @vouchertype = (select VoucherType from GV_VoucherType where VoucherType = 'Value Discount')
BEGIN
INSERT INTO GV_Booklet
VALUES
(
@leaf,
500,
2,
'VD' + Replace( Str( @max-2+1, 7), ' ', '0') ,
@quantity,
GETDATE(),
GETDATE(),
DATEADD(MM,6,GETDATE()),
1,
1,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0')
)
SET @innercount = @innercount + 1
UPDATE gv_maxvouchervalue
SET vouchervaluebased = @max-2 + 1
END
ELSE if @vouchertype = (select VoucherType from GV_VoucherType where VoucherType = 'Percentage Discount')
BEGIN
INSERT INTO GV_Booklet
VALUES
(
@leaf,
500,
3,
'PD' + Replace( Str( @max-2+1, 7), ' ', '0') ,
@quantity,
GETDATE(),
GETDATE(),
DATEADD(MM,6,GETDATE()),
1,
1,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0')
)
SET @innercount = @innercount + 1
UPDATE gv_maxvouchervalue
SET vouchervaluebased = @max-2 + 1
END
END
--SET @quantity = @quantity - 1
SET @innercount = 1
SET @count = @count + 1
UPDATE gv_maxvouchervalue
SET vouchertransactionID = @transactionID + 1
END
select * from GV_Booklet
-- truncate table GV_Booklet
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/