Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

AUTOGENERATE Stored procedures example Expand / Collapse
Author
Message
Posted Monday, November 24, 2008 9:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
Comments posted to this topic are about the item AUTOGENERATE Stored procedures example
Post #608027
Posted Tuesday, April 13, 2010 6:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 13, 2010 6:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 15, 2010 10:10 AM
Points: 3, Visits: 4
Please help me
Post #902314
Posted Thursday, April 15, 2010 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #904196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse