November 24, 2008 at 9:40 pm
Comments posted to this topic are about the item AUTOGENERATE Stored procedures example
April 13, 2010 at 6:02 am
Please, I'm a beginner.
After reading Larry Sumuri tutorial on Seral ID Auto generation, I created a procedure.
create table RefVal_SID
(
SID int not null,
constraint pk_refval_sid primary key clustered (SID asc)
)
create procedure AutogenerateID
@startchar char(1), --first letter
@result varchar(8) output
as
begin
set nocount on;
declare @CurrentSID int
declare @CurrentYear Varchar(4)
declare @UniqueID varchar(8)
declare @Lenght int
declare @GeneratedID int
declare @ConvID varchar(3)
begin try
begin TRAN mytran
set @CurrentSID=(select SID from RefVal_SID)
set @CurrentYear=(select year(getdate()))
if @CurrentSID is null begin
set @CurrentSID=1
end
else
begin
set @CurrentSID=@CurrentSID+1
end
set @GeneratedID=@CurrentSID
set @ConvID=Convert(Varchar(3),@GeneratedID)
set @Lenght=Len(@GeneratedID)
if @Lenght = 1 begin
set @UniqueID = @startchar+@CurrentYear+'00'+@ConvID
end
if @Lenght = 2
begin
set @UniqueID = @startchar+@CurrentYear+'0'+@ConvID
end
if @Lenght=3
set @UniqueID =@startchar+@CurrentYear+@ConvID
insert into RefVal_SID
values(@CurrentSID)
delete from RefVal_SID where SID <>@CurrentSID
COMMIT TRAN mytran
set @Result = @UniqueID
end try
begin catch
rollback tran mytran
set @Result = 'Failed'
end catch
end
I created this table to test the procedure but I don't really know how to execute the procedure inside.
create table test
(
testID varchar(8) as execute AutogenerateID 'T',
myname varchar(25),
constraint pk_test primary key clustered (testID asc)
)
Please help me
April 13, 2010 at 6:02 am
Please help me
April 15, 2010 at 10:10 am
I found the solution myself.
--Autogeneration Procedure
create table RefVal_SID
(
SID int not null,
constraint pk_refval_sid primary key clustered (SID asc)
)
create procedure AutogenerateID
@startchar char(1),
@result varchar(8) output
as
begin
set nocount on;
declare @CurrentSID int
declare @CurrentYear Varchar(4)
declare @UniqueID varchar(8)
declare @Lenght int
declare @GeneratedID int
declare @ConvID varchar(3)
begin try
begin TRAN mytran
set @CurrentSID=(select SID from RefVal_SID)
set @CurrentYear=(select year(getdate()))
if @CurrentSID is null begin
set @CurrentSID=1
end
else
begin
set @CurrentSID=@CurrentSID+1
end
set @GeneratedID=@CurrentSID
set @ConvID=Convert(Varchar(3),@GeneratedID)
set @Lenght=Len(@GeneratedID)
if @Lenght = 1 begin
set @UniqueID = @startchar+@CurrentYear+'00'+@ConvID
end
if @Lenght = 2
begin
set @UniqueID = @startchar+@CurrentYear+'0'+@ConvID
end
if @Lenght=3
set @UniqueID =@startchar+@CurrentYear+@ConvID
insert into RefVal_SID
values(@CurrentSID)
delete from RefVal_SID where SID <>@CurrentSID
COMMIT TRAN mytran
set @Result = @UniqueID
end try
begin catch
rollback tran mytran
set @Result = 'Failed'
end catch
end
--Example
create table StaffDetails
(
StaffID varchar(8) Primary key, --should be autogenerated
Branch_ID varchar(8) NOT NULL, --should be autogenerated
FirstName Char(20) NOT NULL,
LastName char (20) NOT NULL,
Designation varchar(50) NOT NULL,
Address varchar(50)NOT NULL,
Phone_Num char(19) Constraint chkPhoneNum Check(Phone_Num like '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]')NOT NULL
)
create procedure spInsertIntoStaff
@FirstName Char(20) ,
@LastName char (20) ,
@Designation varchar(50) ,
@Address varchar(50),
@Phone_Num char(19)
as
begin
begin try
begin tran mytran
declare @Staff_ID varchar(8)
declare @Branch_ID varchar(8)
EXEC AutogenerateID 'S',@Staff_ID output
EXEC AutogenerateID 'B',@Branch_ID output
insert into StaffDetails
values (@Staff_ID,@Branch_ID,@FirstName,@LastName,@Designation,@Address,@Phone_Num)
commit tran mytran
end try
begin catch
print 'Error'
rollback tran mytran
end catch
end
exec spInsertIntoStaff 'Cedric','Selom','Developer','Planet Jupiter','02-409-5413-400-111'
select * from StaffDetails
I hope it'll help someone. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply