May 8, 2013 at 7:43 am
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:Id BookletID LeafCount Denomination VoucherTypeId VoucherNo Quantity CreatedDate ModifyDate ExpiryDate VoucherStatusId TransactionID AmountValue ModifiedBy CreatedBy IsDeleted ValidateDays
21 B00005 10 110 1 BVB8489797 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
22 B00005 10 110 1 BVB8489798 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
23 B00005 10 110 1 BVB8489799 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
24 B00005 10 110 1 BVB8489800 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
25 B00005 10 110 1 BVB8489801 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
26 B00005 10 110 1 BVB8489802 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
27 B00005 10 110 1 BVB8489803 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
28 B00005 10 110 1 BVB8489804 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
29 B00005 10 110 1 BVB8489805 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
30 B00005 10 110 1 BVB8489806 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
Is there something wrong with it?
yes, only 10 rows i get with a single bookletID
If this result set is from the Booklets query, we should revisit it later when we've finished here, and on the original thread too. My guess is that by the time we're done here, you will know what to do with Booklets.
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
May 8, 2013 at 7:59 am
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:Id BookletID LeafCount Denomination VoucherTypeId VoucherNo Quantity CreatedDate ModifyDate ExpiryDate VoucherStatusId TransactionID AmountValue ModifiedBy CreatedBy IsDeleted ValidateDays
21 B00005 10 110 1 BVB8489797 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
22 B00005 10 110 1 BVB8489798 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
23 B00005 10 110 1 BVB8489799 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
24 B00005 10 110 1 BVB8489800 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
25 B00005 10 110 1 BVB8489801 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
26 B00005 10 110 1 BVB8489802 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
27 B00005 10 110 1 BVB8489803 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
28 B00005 10 110 1 BVB8489804 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
29 B00005 10 110 1 BVB8489805 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
30 B00005 10 110 1 BVB8489806 2 2013-05-08 2013-05-08 18:51:50.970 2013-11-08 18:51:50.970 1 TRN0000048 0 1 1 0 30
Is there something wrong with it?
yes, only 10 rows i get with a single bookletID
If this result set is from the Booklets query, we should revisit it later when we've finished here, and on the original thread too. My guess is that by the time we're done here, you will know what to do with Booklets.
Alas!!:hehe::-) I am done with this Booklet part...
Thanks a lot Chris for your support..:-)
Learn new thing today from you
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 8:04 am
No worries. Here's another code mod, this one is pretty much complete apart from the error handling block. You MUST read the article and write the code.
--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
DECLARE
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
--AS
--BEGIN
SET NOCOUNT ON;
BEGIN TRY
DECLARE
--@vouchertype varchar(20),
@transactionID varchar(20),
--@count int =1,
@VoucherNumber int, -- was @max-2
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
-- table variables receive OUTPUT
--DECLARE @VoucherNumberTable TABLE (vouchervalue INT);
DECLARE @transactionIDTable TABLE (transactionID INT, VoucherAbbreviation VARCHAR(10));
--------------------------------------------------------------------------------------
-- Load some variables. Exit immediately if vouchertype is invalid
--------------------------------------------------------------------------------------
SELECT
@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),
@retval = LEFT(TrimmedVoucherType,1)
+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')
FROM GV_VoucherType v
CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x
WHERE v.VoucherTypeID = @vouchertypeID;
IF @vouchertypenew IS NULL
RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
--------------------------------------------------------------------------------------
-- Non-updating work is done, so open a transaction for the updates/inserts.
--------------------------------------------------------------------------------------
BEGIN TRANSACTION
--------------------------------------------------------------------------------------
-- Read existing transactionID, set next transactionID
-- Read existing VoucherNumber, set next VoucherNumber
-- Save off the current values for the two rows
-- and update to the next available values
-- taking @quantity into account
--------------------------------------------------------------------------------------
UPDATE gv_vouchervalue WITH (TABLOCKX, HOLDLOCK)
SET VoucherValue = CASE
WHEN VoucherAbbreviation = 'TRNID' THEN ISNULL(VoucherValue,0) + 1
WHEN voucherabbreviation = 'V' + @retval THEN ISNULL(VoucherValue,0) + @quantity
END
OUTPUT deleted.vouchervalue, deleted.VoucherAbbreviation
INTO @transactionIDTable
WHERE VoucherAbbreviation IN ('TRNID', 'V' + @retval);
SELECT @transactionID = ISNULL(VoucherValue,0) FROM @transactionIDTable WHERE VoucherAbbreviation = 'TRNID';
SELECT @VoucherNumber = ISNULL(VoucherValue,0) FROM @transactionIDTable WHERE VoucherAbbreviation = 'V' + @retval;
--------------------------------------------------------------------------------------
-- Insert required number of rows into GV_Voucher
-- Inline tally table generates (@quantity) rows
--------------------------------------------------------------------------------------
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 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
iTally(N) AS (
SELECT TOP(@quantity)
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM E2 a, E2 b, E2 c)
-- USE A COLUMN LIST!!
-- If the column order is changed or a column is added or removed,
-- an insert without a column list will fail.
-- A column list is free documentation. What does
-- 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') go into?
INSERT INTO GV_Voucher
SELECT
@vouchertypeID,
'V'+@retval + Replace( Str( @VoucherNumber + t.n, 7), ' ', '0'),
@denomination,
DATEADD(MM,6,GETDATE()),
GETDATE(),
GETDATE(),
@voucherstatus,
'TRN' + REPLACE( Str( @transactionID + 1, 7), ' ', '0'),
@quantity,
CASE @vouchertypenew
WHEN 'Discount' THEN @amountvalue
ELSE 0
END,
@validateDays,
0
FROM iTally t;
--------------------------------------------------------------------------------------
-- No errors, so...
--------------------------------------------------------------------------------------
COMMIT TRANSACTION
RETURN 0
END TRY
--------------------------------------------------------------------------------------
-- Error handling
--------------------------------------------------------------------------------------
BEGIN CATCH
-- stuff in here, refer to the article
RETURN -1
END CATCH
Notice the HOLDLOCK query hint? Remember you wanted this batch to be "serializable"? Look up HOLDLOCK in BOL 😉
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
May 8, 2013 at 8:07 am
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 int = 3,
@amountvalue int =0,
@leaf int =2,
@userid varchar(50) =1,
@ValidateDays int =30
AS
BEGIN
SET NOCOUNT ON;
DECLARE
--@vouchertype varchar(20),
@transactionID varchar(20),
--@count int =1,
--@innercount int =1,
@VoucherNumber int,
--@max int,
@vouchertypenew varchar(20),
@retval varchar(20),
@BookletID int = 0,
@VoucherStatus int
-- table variables receive OUTPUT
DECLARE @VoucherNumberTable TABLE (vouchervalue INT);
DECLARE @transactionIDTable TABLE (transactionID INT);
DECLARE @BookletIDTable TABLE (BookletID int);
SELECT
@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),
@retval = LEFT(TrimmedVoucherType,1)
+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')
FROM GV_VoucherType v
CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x
WHERE v.VoucherTypeID = @vouchertypeID;
--If @vouchertypenew IS NULL
--Return -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';
------------------------------------------------------------------------------
--Read Existing Booklet ID and set next @quantity
------------------------------------------------------------------------------
--SET @BookletID = (SELECT ISNULL(MAX(RIGHT(BookletID,5)),0) FROM GV_Booklet)
Update Gv_VoucherValue
SET VoucherValue = ISNULL(VoucherValue,0)+ @quantity
OUTPUT deleted.VoucherValue INTO @BookletIDTable
WHERE VoucherAbbreviation = 'BOKID'
SELECT @BookletID = ISNULL(BookletID,0) FROM @BookletIDTable
--------------------------------------------------------------------------------------
-- Read existing @VoucherNumber, set next @VoucherNumber
--------------------------------------------------------------------------------------
-- save off the current value to @VoucherNumber
-- and update to the next available value
UPDATE gv_vouchervalue
SET vouchervalue = ISNULL(vouchervalue,0) + @leaf
OUTPUT deleted.vouchervalue INTO @VoucherNumberTable
WHERE voucherabbreviation = 'B' + @retval;
SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;
--------------------------------------------------------------------------------------
-- Read existing @transactionID, set next @transactionID
--------------------------------------------------------------------------------------
-- save off the current value to @transactionID
-- and update to the next available value
UPDATE gv_vouchervalue
SET VoucherValue = VoucherValue + 1
OUTPUT deleted.vouchervalue INTO @transactionIDTable
WHERE VoucherAbbreviation = 'TRNID'
SELECT @transactionID = ISNULL(transactionID,0) FROM @transactionIDTable;
--------------------------------------------------------------------------------------
-- Insert required number of rows into GV_Voucher
--------------------------------------------------------------------------------------
-- Inline tally table generates (@quantity) rows
BEGIN Tran
;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 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E3(N) AS (SELECT 1 FROM E2 a , E2 b, E2 c),
--iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b, E2 c),
Booklets as (SELECT TOP(@quantity) Bookno = @BookletID + ROW_NUMBER() OVER (ORDER BY(SELECT NULL))FROM E2),
Pages AS (SELECT TOP(@leaf) N FROM E3)
--select N from E3
INSERT INTO GV_Booklet
SELECT
'B'+ REPLACE(STR(CONVERT(varchar,BookNo),5),' ','0') ,
@leaf,
@denomination,
@vouchertypeID,
'B' + @retval + REPLACE(STR( CAST(@VoucherNumber + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(10)),7),' ','0'),
--'B' + @retval + CAST(@VoucherNumber + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(10)) ,
@quantity,
GETDATE(),
GETDATE(),
DATEADD(MM,6,GETDATE()),
@VoucherStatus,
'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0'),
@amountvalue,
0,
@ValidateDays
FROM Booklets b
CROSS JOIN Pages p
COMMIT TRAN
--select * from GV_Booklet
END
--truncate table GV_Booklet
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2013 at 8:13 am
kapil_kk (5/8/2013)
oopes I am getting error: 🙁...
Can you post it on the relevant thread please?
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
May 8, 2013 at 8:34 am
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
oopes I am getting error: 🙁...
Can you post it on the relevant thread please?
Ok i have posted this in that thread
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply