Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rudba
Constraint Violating Yak Guru
415 Posts |
 Posted - 2010-01-05 : 09:35:15
|
| Can anybody modify the follwing procedure?I would like to get new id like ABC00001Create PROCEDURE [dbo].[GetNewId]ASSELECT ISNULL(MAX(CONVERT(INT,replace(ID,'ABC',''))),0) +1 AS IdFROM tblA |
|
|
balaganapathy.n
Starting Member
18 Posts |
Posted - 2010-01-05 : 10:11:40
|
| Try this SELECT 'ABC' + SUBSTRING( CONVERT( VARCHAR, MAX(CONVERT(INT, (REPLACE(ISNULL(ID, 'ABC00001') , 'ABC', '')) ) + 100000) + 1 ), 2, 5 )FROM tblBThis wont return any value unless you manually insert a row with value 'ABC00001' in the table tblAbalaganapathy n.Anything you can imagine is real. |
 |
|
|
rudba
Constraint Violating Yak Guru
415 Posts |
Posted - 2010-01-05 : 11:15:31
|
| Thanks Balaganapathy.n what are then last two parameters (2,5) |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-05 : 11:17:58
|
| Those are part of the substring function, start at position 2 and go 5 positions further. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-06 : 03:30:07
|
| also seehttp://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server |
|
|
|
|
|
|
|
|
GetNewID