Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Isolation Level


Isolation Level

Author
Message
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
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: 16539 Visits: 16992
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 Moden's 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32252
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
         Wink
         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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19011
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
      Wink
   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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
      Wink
   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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19011
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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