AUTOGENERATE Stored procedures example

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Chris Morton » AUTOGENERATE Stored procedures example

AUTOGENERATE Stored procedures example

Rate Topic

Display Mode

Topic Options

Author

Message

kodracon

Posted Monday, November 24, 2008 9:40 PM

Valued Member

Group: General Forum Members
Last Login: Sunday, September 25, 2011 6:50 AM
Points: 55, Visits: 155

Comments posted to this topic are about the item AUTOGENERATE Stored procedures example

Post #608027

Selom

Posted Tuesday, April 13, 2010 6:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 15, 2010 10:10 AM
Points: 3, Visits: 4

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

Post #902312

Selom

Posted Tuesday, April 13, 2010 6:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 15, 2010 10:10 AM
Points: 3, Visits: 4

Please help me

Post #902314

Selom

Posted Thursday, April 15, 2010 10:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 15, 2010 10:10 AM
Points: 3, Visits: 4

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. Wink

Post #904196

« Prev Topic | Next Topic »

Permissions