oopes there was problem in this

UPDATE gv_vouchervalue

SET vouchervalue = ISNULL(vouchervalue,0) + (@leaf * @quantity)

OUTPUT deleted.vouchervalue INTO @VoucherNumberTable

WHERE voucherabbreviation = 'B' + @retval;

SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;

in Gv_vouchervalue it...

Hi Chris. I trie to play with tally script ans found that bouvherno are not generating new everytime when we run it.. it is returning same values due to that...

Nice question....

Here are some more scenarios:

--Scenario 1

create table address_staging

(client int primary key,addressdetails varchar(250));

insert into address_staging

select 100,'hyderbad,india'

union all

select 101,'banglore,india'

union all

select 102,'banglore,india'

;

create table address_oltp...

I think the prob is this part:

INSERT INTO GV_Booklet

SELECT

'B'+ REPLACE(STR(CONVERT(varchar,BookNo),5),' ','0') ,

@leaf,

@denomination,

@vouchertypeID,

'B' + @retval + REPLACE(STR(@VoucherNumber + p.N ,7),' ','0'),

--'B' + @retval + CAST(@VoucherNumber + ROW_NUMBER() OVER(ORDER BY...

Ok i have posted this in that thread

oopes I am getting error:

Cannot insert duplicate key row in object 'dbo.GV_Booklet' with unique index 'UN_GVBooklet_VoucherNo'. The duplicate key value is (BVD0002789).

ALTER procedure [dbo].[BS_Voucher_CreateBooklet]

@vouchertypeID int =2,

@denomination int = 555,

@quantity...

oopes I am getting error: 🙁

Cannot insert duplicate key row in object 'dbo.GV_Booklet' with unique index 'UN_GVBooklet_VoucherNo'. The duplicate key value is (BVD0002789).

ALTER procedure [dbo].[BS_Voucher_CreateBooklet]

@vouchertypeID int =2,

@denomination int = 555,

@quantity...

ChrisM@Work (5/8/2013)

---

kapil_kk (5/8/2013)

---

ChrisM@Work (5/8/2013)

---

kapil_kk (5/8/2013)

---

this is the data i get after runing this script:

IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays

21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

29B00005101101BVB848980522013-05-082013-05-08...

ChrisM@Work (5/8/2013)

---

kapil_kk (5/8/2013)

---

this is the data i get after runing this script:

IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays

21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

29B00005101101BVB848980522013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

30B00005101101BVB848980622013-05-082013-05-08...

ChrisM@Work (5/8/2013)

---

kapil_kk (5/8/2013)

---

Hi Chris,

I tried this tally table to my another scenario for creating booklet...

What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno...

this is the data i get after runing this script:

IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays

21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

29B00005101101BVB848980522013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

30B00005101101BVB848980622013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030

Hi Chris,

I tried this tally table to my another scenario for creating booklet...

What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno will get...

ChrisM@Work (5/8/2013)

---

kapil_kk (5/8/2013)

---

One more doubt Chris,

;WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION...

One more doubt Chris,

;WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1...

ChrisM@Work (5/8/2013)

---

Here's what I'd recommend you do.

Firstly, read the error trapping article. If your batch fails in the middle of the transaction then you need a graceful way to...