kapil_kk (3/19/2013)
ChrisM@Work (3/19/2013)
kapil_kk (3/18/2013)
...How to achieve this?plz help
Do you need help with the stub code I posted?
Thanks Chris, I have implemented that thing....
But I am not clear why you posted that code
It's a set-based replacement for this crazy nested loop structure:
WHILE @count <= @quantity
BEGIN
WHILE @innercount < @leaf
BEGIN
SET @max-2 = (select vouchervaluebased from gv_maxvouchervalue)
SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue)
INSERT INTO GV_Booklet
VALUES
(
@leaf,
100,
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
SET @innercount = 1
SET @count = @count + 1
END
The set-based version would be much faster and, in my opinion, considerably easier to maintain.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden