SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Isolation Level


Isolation Level

Author
Message
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 2766
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/
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26536 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41161 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 2766
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
VoucherId VoucherNo TransactionID Quantity AmountValue
4 VVD0000001 TRN0000002 5 400
5 VVD0000002 TRN0000002 5 400
6 VVD0000003 TRN0000002 5 400
7 VVD0000004 TRN0000002 5 400
8 VVD0000005 TRN0000002 5 400
9 VVD0000006 TRN0000003 5 400
10 VVD0000007 TRN0000003 5 400
11 VVD0000008 TRN0000003 5 400
12 VVD0000009 TRN0000003 5 400
13 VVD0000010 TRN0000003 5 400

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 2766
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 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 = (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+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,
@userid,
@userid,
@validateDays,
0
)
SET @count = @count + 1

Update gv_vouchervalue
SET vouchervalue = @max + 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/
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16652 Visits: 19557
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 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 = 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 + @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,
@userid,
@userid,
@validateDays,
0
)
SET @count = @count + 1

--END

END

UPDATE gv_vouchervalue
SET vouchervalue = @max + @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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 2766
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 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 = 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 + @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,
@userid,
@userid,
@validateDays,
0
)
SET @count = @count + 1

--END

END

UPDATE gv_vouchervalue
SET vouchervalue = @max + @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/
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 2766
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/
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16652 Visits: 19557
Which column of the VALUES clause of the INSERT statement is it?

'V'+@retval + Replace( Str( @max + @count, 7), ' ', '0') ?

seems likely, but you have no column list to insert into.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 2766
ChrisM@Work (5/7/2013)
Which column of the VALUES clause of the INSERT statement is it?

'V'+@retval + Replace( Str( @max + @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 value assign.

SELECT @max = 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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search