March 3, 2004 at 7:20 pm
Dear everyone,
Say to create String ID and this ID will be incremented by 1 e.g. A0001 and when new record inserted, the ID will be A0002.
How can I use procedure or trigger to do that??
Thanks you for kind attention!!!!
March 4, 2004 at 11:45 pm
Hi, not really the solution to your question.. but this is how we do it...
We have a table with the following:
TableName, CurrentNumber, EndNumber etc etc
We created 1 Procedure, taking the table name (or any) as parameter:
1. Based on parameter, read into the set up table and take "CurrentNumber"
2. Update CurrentNumber by +1
3. Output the CurrentNumber fetched in step1
I guess the trigger can do something like that too... The good thing is that we can reset the number and we can add as many tables to the set up table
March 5, 2004 at 1:06 am
I would suggest to use a extra identity column to handle this. Here is the output of 2 possible solutions you can find in my test script : (the sp__drop procedures are procs i wrote to drop existing tables/procedures/functions ... ) :
drop table dbo.tst1
create table dbo.tst1
insert dbo.tst1(name)
select * from dbo.tst1
_id ID name
1 A0001 a
2 A0002 b
3 A0003 c
drop table dbo.tst2
drop function dbo.fn_stringID
create function dbo.fn_stringID
test dbo.fn_stringID
dbo.fn_stringID(1) = A0001
dbo.fn_stringID(9999) = A9999
dbo.fn_stringID(10000) = B0000
insert dbo.tst2(name)
select * from dbo.tst2
_id ID name
1 A0001 a
2 A0002 b
3 A0003 c
SCRIPT :
/*
** solution 1 : computed column -> After ID A9999 you will have A0001 again
*/
set nocount on
go
sp__droptable 'dbo.tst1'
go
print 'create table dbo.tst1'
go
create table dbo.tst1
( _id int identity(1,1) PRIMARY KEY CLUSTERED NOT NULL,
ID as 'A' + right('0000'+convert(varchar(12),_id),4),
name varchar(30) not null
)
go
print 'insert dbo.tst1(name)'
go
insert dbo.tst1(name) values ('a')
insert dbo.tst1(name) values ('b')
insert dbo.tst1(name) values ('c')
go
print 'select * from dbo.tst1'
go
select * from dbo.tst1
go
/*
** solution 2 : use a function to handle the logic
*/
go
sp__droptable 'dbo.tst2'
go
sp__dropfunction 'dbo.fn_stringID'
go
print 'create function dbo.fn_stringID'
go
create function dbo.fn_stringID ( @ID int ) returns char(5) as
begin
declare
@stringID char(5),
@firstchar char(1),
@alfabet char(26)
set @alfabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
set @firstchar = substring(@alfabet,(@ID / 10000)+1,1)
set @stringID = @firstchar + right('0000'+convert(varchar(12),@ID % 10000),4)
return @stringID
end
go
print 'test dbo.fn_stringID'
go
print 'dbo.fn_stringID(1) = ' + dbo.fn_stringID(1)
print 'dbo.fn_stringID(9999) = ' + dbo.fn_stringID(9999)
print 'dbo.fn_stringID(10000) = ' + dbo.fn_stringID(10000)
go
create table dbo.tst2
( _id int identity(1,1) PRIMARY KEY CLUSTERED NOT NULL,
ID as dbo.fn_stringID(_id),
name varchar(30) not null
)
go
print 'insert dbo.tst2(name)'
go
insert dbo.tst2(name) values ('a')
insert dbo.tst2(name) values ('b')
insert dbo.tst2(name) values ('c')
go
print 'select * from dbo.tst2'
go
select * from dbo.tst2
go
March 5, 2004 at 10:24 am
Is the id limited to A0001, A0002, ..., A9999? Or does A9999 get incremented to B0000 or B0001? If the A is fixed, you can obtain the next value using:
(SELECT Left(Max(id),1) FROM myTable)
+ Right('0000' + (SELECT Convert(varchar(4), Convert(int, Right(Max(id),4))+1) FROM myTable) ,4)
-----------------------------------------------------
For example:
create table test (cid char(5) PRIMARY KEY, data varchar(20))
insert test (cid, data) values ('A0001', 'Some test data')
declare @cid char(5), @loopcount int
set @loopcount = 1
while @loopcount < 10
begin
set @cid = (SELECT Left(Max(cid),1) FROM test)
+ Right('0000' + (SELECT Convert(varchar(4), Convert(int, Right(Max(cid),4))+1) FROM test) ,4)
insert test (cid, data) values (@cid, 'Some more test data')
Set @loopcount = @loopcount + 1
end
select * from test order by cid
March 5, 2004 at 10:04 pm
Thanks you for reply!!
I am the first time to create triggers or procedure.
Do u mean that I need to insert A0001 first and then run the while loop command??
And in where I should put this code in SQL Server??
Thanks you very much!!!
March 6, 2004 at 11:59 am
The loop was just to show that the code worked correctly. I would not use a trigger if the ID is the primary key for the table. A primary key cannot allow null values. Use a stored procedure instead. Here is a new example:
(After executing the full example once, run the last four lines over and over again to see the ID increment).
DROP TABLE test
GO
CREATE TABLE test (cid char(5) PRIMARY KEY, data varchar(20))
GO
DROP PROCEDURE uspTestInsertRow
GO
CREATE PROCEDURE uspTestInsertRow
(
@newid varchar(5) OUTPUT
)
AS
-- This procedure inserts a blank row into table TEST
SET @newid = (SELECT Left(IsNull(Max(cid), 'A'),1) FROM test)
+ Right('0000' + (SELECT Convert(varchar(4), Convert(int, Right(IsNull(Max(cid),1),4))+1) FROM test) ,4)
INSERT test (cid, data) VALUES (@newid, 'Some more test data')
GO
------------------------------------------------------------------------------
-- After creating stored procedure uspTestInsertRow, test with the following
------------------------------------------------------------------------------
DECLARE @cid varchar(5)
EXEC uspTestInsertRow @cid OUTPUT
PRINT @cid
SELECT * FROM test ORDER BY cid
March 6, 2004 at 10:33 pm
Thanks you for your reply!!!
All the code work fine!!!
But I have one more question:
How can I add conditional if statement in the procedure??
I would like to use letter 'B' for the first character of the stringID such as 'B0001'??
Thanks a lot !!!!!!!!!!!!!!!
March 8, 2004 at 11:28 am
This version starts with A0001, then procedes to A9999, B0001, B0002,...,B9999, C0001, etc. The loop at the end is just for testing purposes. It will insert 10005 rows to show the rollover from 'A' to 'B'.
This can be modified to use the 'zero' values A0000, B0000, etc.
DROP TABLE test
GO
CREATE TABLE test (cid char(5) PRIMARY KEY, data varchar(20))
GO
DROP PROCEDURE uspTestInsertRow
GO
CREATE PROCEDURE uspTestInsertRow
(
@newid varchar(5) OUTPUT
)
AS
DECLARE @newidChar char(1), @newidNumber int
-- This procedure inserts a blank row into table TEST
SET @newidChar = (SELECT Left(IsNull(Max(cid), 'A'),1) FROM test)
SET @newidNumber = (SELECT Convert(int, Right(IsNull(Max(cid),0),4)) + 1 FROM test)
IF @newidNumber > 9999
BEGIN
SET @newidChar = Char( Ascii(@newidChar) + 1)
SET @newidNumber = 1
END
SET @newid = @newidChar + Right('0000' + Convert(varchar(4), @newidNumber) ,4)
INSERT test (cid, data) VALUES (@newid, 'Some more test data')
GO
------------------------------------------------------------------------------
-- After creating stored procedure uspTestInsertRow, test with the following
------------------------------------------------------------------------------
SET NOCOUNT ON
--- this loop will create a few rows for which
-- the id wraps around A9999, B0001, B0002...
declare @loopCount int, @maxloopcount int
set @loopcount = 1
set @maxloopcount = 10005
DECLARE @cid varchar(5)
WHILE @loopcount <= @maxloopcount
BEGIN
EXEC uspTestInsertRow @cid OUTPUT
-- PRINT @cid
SET @loopcount = @loopcount + 1
END
SET NOCOUNT OFF
SELECT * FROM test ORDER BY cid
March 8, 2004 at 2:46 pm
CREATE TABLE test(
IntID int identity(0,1) CHECK (IntID < 260000),
StringID AS CHAR(IntID/10000 + 65) + RIGHT('0000' + CAST(IntID AS varchar(10)),4))
You could also do this with a view instead of a computed column.
--Jonathan
March 9, 2004 at 7:35 am
Thanks you for reply!! It works fine
But I would like to add first letter 'B' in the StringID for special use.
How can I add conditional statement to control?? The first letter will be entered by user.
March 9, 2004 at 7:53 am
If the first character is input but the numeric portion is automatically increasing, then you could use bbron's suggestion of a table holding the last number used.
CREATE TABLE SeqNos(
Type char(4) PRIMARY KEY,
LastUsed int NOT NULL)
CREATE PROC GetNextNo @Type char(4) = 'TEST', @SeqNo int OUTPUT AS
SET NOCOUNT ON
UPDATE SeqNos SET @SeqNo = LastUsed = LastUsed + 1
WHERE Type = @Type
Actually, I would use another column in your table for the prefix and an identity column for the numeric piece, and then just paste them together at the presentation layer or by using a computed column or view. This would be properly normalized, as the prefix evidently represents another quantum of information.
--Jonathan
March 9, 2004 at 8:03 am
Thanks!!
If the first character and numeric portion will be at least entered once and then automatically increasing, can I modify your SQL create statement to do that??? When another new one, e.g. C0001 is inputted, the stringID will be auto increased based on C0001.
Waiting for reply!!!
March 9, 2004 at 8:51 am
It now sounds as though you should not be using a method incorporating the identity property. Alter the stored procedure so it optionally takes a prefix and/or number.
CREATE TABLE SeqNos(
Prefix char CHECK (Prefix LIKE '[A-Z]'),
Num smallint NOT NULL CHECK (Num BETWEEN 0 AND 9999))
go
CREATE PROC GetNextNo @SeqNo char(5) OUTPUT, @Prefix char(1) = NULL, @Num smallint = NULL AS
SET NOCOUNT ON
BEGIN TRAN
UPDATE SeqNos SET Num = ISNULL(@Num,CASE Num WHEN 9999 THEN 0 ELSE Num + 1 END),
Prefix = ISNULL(UPPER(@Prefix),CASE Num WHEN 9999 THEN CHAR(ASCII(Prefix)+1) ELSE Prefix END)
SET @SeqNo = (SELECT Prefix + REPLACE(STR(Num,4),' ','0') FROM SeqNos)
COMMIT
--Jonathan
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply