user defined data type - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

user defined data type

Author

Topic

ballabhoks
Starting Member

18 Posts

Posted - 2004-03-20 : 01:26:41

Dear All,
Can we create a user defined data type which will accept values in following manner:-

JT2_0001
JT2_0002
.....
.....
.....
.....
.....
JT2_0100

I want last three numbers gets generated by the system and gets incremented everytime, I insert a new record

Please help.....

Thanks in advance

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-20 : 06:17:52

I'm Not Sure that a new datatype can be created.

It is possible using a user defined function though.

This may be a bit cpu intensive though, but something like this might do the trick.....

--*******************************************************************************************
CREATE TABLE MyTable00(MainID VARCHAR(10),
MainDetail VARCHAR(100))
GO
INSERT INTO MyTable00
SELECT 'JT2_0001', 'Blah Blah' UNION ALL
SELECT 'JT2_0002', 'Blah Blah' UNION ALL
SELECT 'JT2_0003', 'Blah Blah' UNION ALL
SELECT 'JT2_0004', 'Blah Blah' UNION ALL
SELECT 'JT2_0005', 'Blah Blah' UNION ALL
SELECT 'JT2_0006', 'Blah Blah' UNION ALL
SELECT 'JT2_0007', 'Blah Blah' UNION ALL
SELECT 'JT2_0008', 'Blah Blah' UNION ALL
SELECT 'JT2_0009', 'Blah Blah' UNION ALL
SELECT 'JT2_0010', 'Blah Blah'

GO

CREATE FUNCTION NextID() RETURNS VARCHAR(10)
AS

BEGIN
DECLARE @MaxID as VARCHAR(10)
DECLARE @MaxNum as INT

SET @MaxID = (SELECT MAX(MainID) FROM MyTable00)
SET @MaxNum = CAST(RIGHT(@MaxID, 4) as INT)

SET @MaxNum = @MaxNum + 1

RETURN LEFT(@MaxID, 4) + RIGHT('0000' + LTRIM(STR(@MaxNum, 4)),4)
END

GO

INSERT INTO MyTable00
SELECT dbo.NextID(), 'Inserted Blah'

GO

SELECT * FROM mytable00

--****************************************************************

What do you think?

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources