Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Isolation Level Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 7:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1449710
Posted Monday, May 6, 2013 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,069, Visits: 11,907
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)
Post #1449726
Posted Monday, May 6, 2013 12:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 15,517, Visits: 27,896
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1449830
Posted Monday, May 6, 2013 11:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1449980
Posted Tuesday, May 7, 2013 12:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1450001
Posted Tuesday, May 7, 2013 5:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1450061
Posted Tuesday, May 7, 2013 6:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1450089
Posted Tuesday, May 7, 2013 6:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1450090
Posted Tuesday, May 7, 2013 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1450092
Posted Tuesday, May 7, 2013 7:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1450124
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse