May 6, 2013 at 7:27 am
Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every insertion there is a ID generation for the rows like if a quantity is 100 then for 100 rows there will be a single transaction ID as there will be 100 unique vouchers. Suppose if I inserted 1000 rows who should get ID 001 but on another screen I am again performing inserting then it should get another unique Id say 002 but I am getting same ID for both windows.
How to achieve this plz tell?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 6, 2013 at 7:57 am
kapil_kk (5/6/2013)
Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every insertion there is a ID generation for the rows like if a quantity is 100 then for 100 rows there will be a single transaction ID as there will be 100 unique vouchers. Suppose if I inserted 1000 rows who should get ID 001 but on another screen I am again performing inserting then it should get another unique Id say 002 but I am getting same ID for both windows.How to achieve this plz tell?
Kapil I am not quite understanding what you are doing here. Can you please try to explain more clearly the situation?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 6, 2013 at 12:08 pm
If we're talking about a table that records the transaction and a table that stores the list of transactions, assuming you want to automatically generate the ID on the transaction table and then use it on the other table, I'd suggest looking at using an identity column on that first table. You can insert the value and then use scope_identity to capture the value for use on the insert into the second table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2013 at 11:23 pm
Sean Lange (5/6/2013)
kapil_kk (5/6/2013)
Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every insertion there is a ID generation for the rows like if a quantity is 100 then for 100 rows there will be a single transaction ID as there will be 100 unique vouchers. Suppose if I inserted 1000 rows who should get ID 001 but on another screen I am again performing inserting then it should get another unique Id say 002 but I am getting same ID for both windows.How to achieve this plz tell?
Kapil I am not quite understanding what you are doing here. Can you please try to explain more clearly the situation?
Here is the some sample data of some columns from my table.. See if quantity is 5 then 5 unique voucher no gets genearted but they have same transaction Id. the problem that I ma facing is that if I entered quantity >1000 and while inserting if start inserting in another window for quantity e.g 100 then both these insertion gets same transaction ID.
Hope its clear to you now
VoucherIdVoucherNoTransactionIDQuantityAmountValue
4VVD0000001TRN00000025400
5VVD0000002TRN00000025400
6VVD0000003TRN00000025400
7VVD0000004TRN00000025400
8VVD0000005TRN00000025400
9VVD0000006TRN00000035400
10VVD0000007TRN00000035400
11VVD0000008TRN00000035400
12VVD0000009TRN00000035400
13VVD0000010TRN00000035400
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 12:41 am
Here is my stored procedure script in which I have to achive isolation:
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
BEGIN
SET NOCOUNT ON;
DEclare
@vouchertype varchar(20),
@transactionID varchar(20),
@count int =1,
@max-2 int,
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
SET @vouchertype = (SELECT VoucherType FROM GV_VoucherType where VoucherTypeID = @vouchertypeID)
SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
SET @voucherstatus = (SELECT gvs.VoucherStatusId FROM GV_VoucherStatus gvs WHERE gvs.VoucherStatus = 'New')
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END
WHILE @count <= @quantity
BEGIN
SET @max-2 = (select ISNULL(vouchervalue,0) from gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval )
SET @transactionID = (select ISNULL(vouchervalue,0) from gv_vouchervalue where voucherabbreviation = 'TRNID')
If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
BEGIN
INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @max-2+1, 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
)
SET @count = @count + 1
Update gv_vouchervalue
SET vouchervalue = @max-2 + 1
WHERE voucherabbreviation = 'V'+@retval
END
END
UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 5:06 am
You're having problems with this mainly because you are working with one row at a time. Your control table is updated once for each row inserted, instead of updating it once with the whole range of numbers required. We've been here before - you're very new to SQL so let's try and work this through. I think that converting this stored procedure to a set-based equivalent would be best achieved in two steps, with the first being a cleanup and re-arrangement of the code, like this:
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
--BEGIN
SET NOCOUNT ON;
DECLARE
@vouchertype varchar(20),
@transactionID varchar(20),
@count int =1,
@max-2 int,
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
SELECT @vouchertype = VoucherType FROM GV_VoucherType WHERE VoucherTypeID = @vouchertypeID
IF @vouchertype IS NULL
RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New'
SELECT @transactionID = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'TRNID'
SELECT @max-2 = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval
SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END
WHILE @count <= @quantity
BEGIN
--If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--BEGIN
INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @max-2 + @count, 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
)
SET @count = @count + 1
--END
END
UPDATE gv_vouchervalue
SET vouchervalue = @max-2 + @count
WHERE voucherabbreviation = 'V'+@retval
UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'
--END
RETURN 0
Test it. If there are errors, list them in detail. When it's working, it's quite straightforward to perform step 2.
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 7, 2013 at 6:02 am
ChrisM@Work (5/7/2013)
You're having problems with this mainly because you are working with one row at a time. Your control table is updated once for each row inserted, instead of updating it once with the whole range of numbers required. We've been here before - you're very new to SQL so let's try and work this through. I think that converting this stored procedure to a set-based equivalent would be best achieved in two steps, with the first being a cleanup and re-arrangement of the code, like this:
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
--BEGIN
SET NOCOUNT ON;
DECLARE
@vouchertype varchar(20),
@transactionID varchar(20),
@count int =1,
@max-2 int,
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
SELECT @vouchertype = VoucherType FROM GV_VoucherType WHERE VoucherTypeID = @vouchertypeID
IF @vouchertype IS NULL
RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New'
SELECT @transactionID = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'TRNID'
SELECT @max-2 = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval
SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END
WHILE @count <= @quantity
BEGIN
--If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--BEGIN
INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @max-2 + @count, 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
)
SET @count = @count + 1
--END
END
UPDATE gv_vouchervalue
SET vouchervalue = @max-2 + @count
WHERE voucherabbreviation = 'V'+@retval
UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'
--END
RETURN 0
Test it. If there are errors, list them in detail. When it's working, it's quite straightforward to perform step 2.
Hi Chris,
when I run your query it gives me error:
Cannot insert the value NULL into column 'VoucherNo', table 'GVApp.dbo.GV_Voucher'; column does not allow nulls. INSERT fails.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 6:02 am
Here is the table structure of GV_Voucher for your reference
USE [GVApp]
GO
/****** Object: Table [dbo].[GV_Voucher] Script Date: 05/07/2013 17:32:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GV_Voucher](
[VoucherId] [int] IDENTITY(1,1) NOT NULL,
[VoucherTypeId] [int] NOT NULL,
[VoucherNo] [varchar](20) NOT NULL,
[Denomination] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[CreatedDate] [date] NULL,
[ModifyDate] [datetime] NULL,
[VoucherStatusId] [int] NOT NULL,
[TransactionID] [varchar](20) NOT NULL,
[Quantity] [int] NOT NULL,
[AmountValue] [int] NULL,
[CreatedBy] [nvarchar](50) NULL,
[ModifiedBy] [nvarchar](50) NULL,
[Validatedays] [int] NULL,
[IsDeleted] [bit] NULL,
CONSTRAINT [PK_GV_Voucher] PRIMARY KEY CLUSTERED
(
[VoucherId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_GVVoucher_VoucherNo] UNIQUE NONCLUSTERED
(
[VoucherNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_Vocuher_TransactionID] UNIQUE NONCLUSTERED
(
[VoucherNo] ASC,
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[GV_Voucher] WITH NOCHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus] FOREIGN KEY([VoucherStatusId])
REFERENCES [dbo].[GV_VoucherStatus] ([VoucherStatusId])
GO
ALTER TABLE [dbo].[GV_Voucher] NOCHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus]
GO
ALTER TABLE [dbo].[GV_Voucher] WITH NOCHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherType] FOREIGN KEY([VoucherTypeId])
REFERENCES [dbo].[GV_VoucherType] ([VoucherTypeID])
GO
ALTER TABLE [dbo].[GV_Voucher] NOCHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherType]
GO
ALTER TABLE [dbo].[GV_Voucher] ADD CONSTRAINT [DF_GV_Voucher_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 6:09 am
Which column of the VALUES clause of the INSERT statement is it?
'V'+@retval + Replace( Str( @max-2 + @count, 7), ' ', '0') ?
seems likely, but you have no column list to insert into.
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 7, 2013 at 7:15 am
ChrisM@Work (5/7/2013)
Which column of the VALUES clause of the INSERT statement is it?'V'+@retval + Replace( Str( @max-2 + @count, 7), ' ', '0') ?
seems likely, but you have no column list to insert into.
Yes I have not provided column list while inserting as I am inserting in all columns of a table...
and i think error is coming becouse as per your script @retval is not initilazed and it is using in @max-2 value assign.
SELECT @max-2 = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval
Also can you please tell me that I assgin values to variables using SET but changed them to SELECT... Is there any difference between them?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 7:17 am
The only issue I have in my script is how to handle transaction in this script.....
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
BEGIN
SET NOCOUNT ON;
DEclare
@vouchertype varchar(20),
@transactionID varchar(20),
@count int =1,
@max-2 int,
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
SET @vouchertype = (SELECT VoucherType FROM GV_VoucherType where VoucherTypeID = @vouchertypeID)
SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
SET @voucherstatus = (SELECT gvs.VoucherStatusId FROM GV_VoucherStatus gvs WHERE vs.VoucherStatus = 'New')
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END
WHILE @count <= @quantity
BEGIN
SET @max-2 = (select ISNULL(vouchervalue,0) from gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval )
SET @transactionID = (select ISNULL(vouchervalue,0) from gv_vouchervalue where voucherabbreviation = 'TRNID')
If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
BEGIN
INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @max-2+1, 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
)
SET @count = @count + 1
Update gv_vouchervalue
SET vouchervalue = @max-2 + 1
WHERE voucherabbreviation = 'V'+@retval
END
END
UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 7:21 am
Yes you're right:
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
--BEGIN
SET NOCOUNT ON;
DECLARE
@vouchertype varchar(20),
@transactionID varchar(20),
@count int =1,
@VoucherNumber int, -- was @max-2
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
SELECT @vouchertype = VoucherType FROM GV_VoucherType WHERE VoucherTypeID = @vouchertypeID
IF @vouchertype IS NULL
RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New'
SELECT @transactionID = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'TRNID'
SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END
SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval
WHILE @count <= @quantity
BEGIN
--If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--BEGIN
INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @VoucherNumber + @count, 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
)
SET @count = @count + 1
--END
END
UPDATE gv_vouchervalue
SET vouchervalue = @VoucherNumber + @count
WHERE voucherabbreviation = 'V'+@retval
UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'
--END
RETURN 0
Can you post some values of VoucherType FROM GV_VoucherType?
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 7, 2013 at 8:19 am
ChrisM@Work (5/7/2013)
Yes you're right:
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
--BEGIN
SET NOCOUNT ON;
DECLARE
@vouchertype varchar(20),
@transactionID varchar(20),
@count int =1,
@VoucherNumber int, -- was @max-2
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int
SELECT @vouchertype = VoucherType FROM GV_VoucherType WHERE VoucherTypeID = @vouchertypeID
IF @vouchertype IS NULL
RETURN -1
SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New'
SELECT @transactionID = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'TRNID'
SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END
SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval
WHILE @count <= @quantity
BEGIN
--If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--BEGIN
INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @VoucherNumber + @count, 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
)
SET @count = @count + 1
--END
END
UPDATE gv_vouchervalue
SET vouchervalue = @VoucherNumber + @count
WHERE voucherabbreviation = 'V'+@retval
UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'
--END
RETURN 0
Can you post some values of VoucherType FROM GV_VoucherType?
VoucherTypeID Name
1 ValueBased
2 value Discount
3 PercentageDiscount
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 8:57 am
Thanks. Try this. With multiple dml statements it still requires a transaction and error handling:
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
--BEGIN
SET NOCOUNT ON;
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);
--------------------------------------------------------------------------------------
-- 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';
--------------------------------------------------------------------------------------
-- 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 = vouchervalue + @quantity
OUTPUT deleted.vouchervalue INTO @VoucherNumberTable
WHERE voucherabbreviation = 'V' + @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(vouchervalue,0) FROM @transactionIDTable;
--------------------------------------------------------------------------------------
-- 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 n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
-- 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 TOP(@quantity)
@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;
--UPDATE gv_vouchervalue
--SET vouchervalue = @VoucherNumber + @count
--WHERE voucherabbreviation = 'V'+@retval
--UPDATE gv_vouchervalue
--SET VoucherValue = @transactionID + 1
--WHERE VoucherAbbreviation = 'TRNID'
--END
RETURN 0
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 7, 2013 at 11:47 pm
ChrisM@Work (5/7/2013)
Thanks. Try this. With multiple dml statements it still requires a transaction and error handling:
ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int = 100,
@userid varchar(50) = 1,
@validateDays int = 40
AS
--BEGIN
SET NOCOUNT ON;
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);
--------------------------------------------------------------------------------------
-- 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';
--------------------------------------------------------------------------------------
-- 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 = vouchervalue + @quantity
OUTPUT deleted.vouchervalue INTO @VoucherNumberTable
WHERE voucherabbreviation = 'V' + @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(vouchervalue,0) FROM @transactionIDTable;
--------------------------------------------------------------------------------------
-- 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 n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
-- 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 TOP(@quantity)
@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;
--UPDATE gv_vouchervalue
--SET vouchervalue = @VoucherNumber + @count
--WHERE voucherabbreviation = 'V'+@retval
--UPDATE gv_vouchervalue
--SET VoucherValue = @transactionID + 1
--WHERE VoucherAbbreviation = 'TRNID'
--END
RETURN 0
Thanks a lot Chris.. but I have some queries..
If I once run this script then it will generate rows but if again I run this script then it trow an error:
Violation of UNIQUE KEY constraint 'UN_GVVoucher_VoucherNo'. Cannot insert duplicate key in object 'dbo.GV_Voucher'. The duplicate key value is (VVB0000001).
Also, I am not clear what this will do please explain.. I also want to learn error handling and transaction for this script:
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 n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply