Increment value - 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

General SQL Server Forums

New to SQL Server Programming

Increment value

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-11-01 : 06:24:39

Hi,

I have a problem, I have a column name on my table called ProjectID.
This will be an incremental value, so I set it up as numeric(18,0) data type and set Identity Specification (Is Identity) to YES and the column set as Primary key. It work's fine for the numeric value.

Therefore, it will be incrementing the number 1,2,3,4 .....

Now I have a problem, the ProjectID isnt a numeric value whereby,
I have a contact value plus the increment; meaning the ProjectID will have values such as GCDD_1, GCDD_2, GCDD_3, GCDD_4.

Notice the GCDD_ is a dafault value which is then incremented. Is this possible to be done in SQL table.

Please help!!

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 06:35:23

You could create a project type and hold it separately.
Then you can calculate the incremental value when you need it.

Note that an identity shouldn't be considered as a sequence - it can have gaps.

You could also use a trigger to maintain this value - but better to use an SP for the creation and to calculate the value there.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-11-01 : 06:39:32

any idea if I can use a user define function?
Or sample of SP.. and when should the SP be triggered as this data will be loaded to the database via a front end Vb.Net application..

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 06:39:39

see

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 06:54:53

Depends a lot on how they are loaded - is it one at a time?
To prevent blocking you might want to hold the latest ProjectSeq on another table.
Could have ProjectType + '_' + convert(varchar(10),ProjectSeq) as a calculated column.

create proc s_AddProject
@ProjectType varchar(10) ,
@ProjectID output
as
begin try
Declare @id int
begin tran
select @id = coalesce(max(ProjectSeq),0) from tbl (TABLOCKX, HOLDLOCK)
where ProjectType = @ProjectType
insert tbl (ProjectType, ProjectSeq)
select @ProjectType, @id
commit tran
select @ProjectID = @ProjectType + '_' + convert(varchar(10),@id)
end try
begin catch
rollback tran
raiserror('failed',16,-1)
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-11-01 : 06:56:50

Ok - Is the user defined written fine for what I want to achieve

create function NextProjectID() 
returns char(5) 
as 
begin 
	declare @lastval char(5) 
	set @lastval = (select max(ProjectID) from dbo.Testincrement) 
	if @lastval is null set @lastval = 'GCDD-1' 
	declare @i int 
	set @i = right(@lastval,6) + 1 
	return 'GCDD' + right('-' + convert(varchar(10),@i),1) 
end

If so, how can i call it whenever a new record is added at the front end.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-11-01 : 07:01:03

nigelrivett- thanks for the SP - but this will have to be done by additional Project Type - I dont want to add another column to the database.. Is it still possible..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 07:12:33

Where you see ProcectType you can use left(ProjectID,charindex('_',ProjectID)-1)
And for ProjectSeq right(ProjectType,charindex('_',reverse(ProjectID))-1)

It will mean table scanning to get the maximum current value though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-01 : 07:19:58

You can use computed column.
Refer: http://msdn.microsoft.com/en-us/library/ms191250.aspx

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources