CODEWITHSUNDEEP
asp.netsql-serversql-server-2008sql-server-2005ado.net
Teju MB
Teju MB

Reputation: 1383

How to generate a column with id which increments on every insert

This is my table where i want my PNRNo to be generated as 'PNRRES001' for the first entry, and consecutive entries with 'PNRRES002','PNRRES002' so on. So while creating table only i called that column to function which will generate the PNR no, User just has to enter the CustomerNo from the front end, and data wit PNR & Customer No will updated to the PNRDetails table.

CREATE TABLE PNRDetails(PNRNo AS (DBO.FuncIncPNR()) ,customerNo INT

--FUNCTION TO GENERATE THE PNR NUMBER
ALTER FUNCTION dbo.FuncIncPNR()
RETURNS VARCHAR(20)
AS 
BEGIN
DECLARE @RR VARCHAR(20) SET @RR='PNRRESA001'
--here i have checked if no value is there then return the first value as 'PNRRESA001'
    IF((SELECT COUNT(*)FROM PNRDetails)=0)
    BEGIN
    RETURN @RR
    END
    ELSE
-- if any value is there then take the last value and add 1 to it and update to the table
    BEGIN
    DECLARE @pnr VARCHAR(20),@S1 VARCHAR(20),@S2 INT
    DECLARE PNRCursor CURSOR Static
    FOR SELECT PNRNo FROM PNRDetails
    OPEN PNRCursor
    FETCH LAST FROM PNRNo INTO @pnr
    SET @S1=SUBSTRING(@pnr,1,7)
    SET @S2=RIGHT(@PNR,3)
    SET @S2=@S2+1;
    SET @pnr=@S1+@S2;

    END
    RETURN @pnr
END

--Here am inserting only customerNo as 5 and the PNR should be generated by my function
INSERT INTO PNRDetails VALUES(5)
--it shows 1 row updated  :)
SELECT * FROM PNRDetails
-- but when i run select command it shows
--Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).  :(

U can run this.And pls do help if u find anything that could help me. any help will be appreciated... Waiting for your kind response...

Upvotes: 0

Views: 890

Answers (5)

Kaf
Kaf

Reputation: 33829

If you are happy to change the table structure. Following will do the job.

CREATE TABLE [dbo].[PNRDetails](
[autoId] [int] IDENTITY(1,1) NOT NULL,
[prnNo]  AS ('PNRRES'+right('000'+CONVERT([varchar](3),[dbo].[GetRowCount]([autoId]),(0)),(3))),
[customerNo] [int] NOT NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
[autoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

EDIT: to address identity issue for your requirement pls create following function and pass the [autoId] in as above (edited) in the computed column.

CREATE FUNCTION dbo.GetRowCount
(
@autoId INT
)
RETURNS INT
AS
BEGIN
DECLARE @RESULTS AS INT
SELECT @RESULTS = COUNT(autoId) FROM PNRDetails WHERE PNRDetails.autoId<@autoId
    RETURN @RESULTS + 1
END
GO

--INSERT

INSERT INTO PNRDetails (customerNo) VALUES(5)

Upvotes: 1

Jaques
Jaques

Reputation: 2287

Change the way your trigger works. Something like this

CREATE FUNCTION dbo.fn_FuncIncPNR(@ID int)
RETURNS varchar(20)
BEGIN
Declare @Retval varchar(20),
        @No varchar(4)
Select @No = convert(varchar(4), @ID)
while Len(@No) < 4
    Select @No = '0' + @No      

Select @Retval = 'PNRRESA' + @No
RETURN @Retval
END

You will notice there is a parameter field

Change your table create to this 

CREATE TABLE PNRDetails(PNRNo AS (dbo.fn_ShowPNRNo(wID)), wID int IDENTITY(1,1) NOT NULL, customerNo INT)

That should solve your problem

Upvotes: 0

Zo Has
Zo Has

Reputation: 13038

1) You can use an identity column in your database (INTEGER)

PROS: easy/No gaps in between generated ids

CONS: You have to select the inserted id & return via procedure/query if you were to show it to end user

2) Define a database sequence

PROS: easy to implement/Can be stored/shown to user before the form is even saved

CONS: Gaps in between if the certain id is once generated & not used

3). Select max(id) from column + 1

PROS: Useful where only single user inserts in a table

CONS: disastrous if you were in an environment where multiple users were inserting in the same tablle (mismatched max ids)

4) Use a database trigger to autoincrement the column

PROS:automated

CONS: hard to debug (you have to make sure it don't breaks for some reason otherwise insert fails)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

You could try to use a computed column and an identity column instead.

create table PNRDetails
(
  ID int identity,
  PNRNo as 'PNRRES'+right(1000+ID, 3),
  customerNo int
)

Upvotes: 6

AdaTheDev
AdaTheDev

Reputation: 147264

I would suggest just using an IDENTITY instead as your id, let SQL Server handle the assignment of each id number with all it's built-in guards for concurrency, and leave the formatting up to the UI....or, create a computed column that defines the formatted version of the ID if you really do need it in the DB.

The risk you run with your intended approach is:

  • poor performance
  • concurrency issues - if loats of ids are being generate around the same time

Upvotes: 5

Related Questions