SQLServerCentral / Discuss content posted by Chris Morton / Article Discussions / Article Discussions by Author / AUTOGENERATE Stored procedures example / Latest Posts

RE: AUTOGENERATE Stored procedures example

Selom — Thu, 15 Apr 2010 10:10:52 GMT

I found the solution myself.--Autogeneration Procedurecreate 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) outputasbeginset nocount on;declare @CurrentSID intdeclare @CurrentYear Varchar(4)declare @UniqueID varchar(8)declare @Lenght intdeclare @GeneratedID intdeclare @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_SIDvalues(@CurrentSID)delete from RefVal_SID where SID <>@CurrentSIDCOMMIT TRAN mytranset @Result = @UniqueIDend trybegin catchrollback tran mytranset @Result = 'Failed'end catchend--Examplecreate table StaffDetails(StaffID varchar(8) Primary key, --should be autogeneratedBranch_ID varchar(8) NOT NULL, --should be autogeneratedFirstName 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)asbeginbegin trybegin tran mytrandeclare @Staff_ID varchar(8)declare @Branch_ID varchar(8)EXEC AutogenerateID 'S',@Staff_ID outputEXEC AutogenerateID 'B',@Branch_ID outputinsert into StaffDetailsvalues (@Staff_ID,@Branch_ID,@FirstName,@LastName,@Designation,@Address,@Phone_Num)commit tran mytranend trybegin catchprint 'Error'rollback tran mytranend catchendexec spInsertIntoStaff 'Cedric','Selom','Developer','Planet Jupiter','02-409-5413-400-111'select * from StaffDetailsI hope it'll help someone. ;-)

RE: AUTOGENERATE Stored procedures example

Selom — Tue, 13 Apr 2010 06:02:25 GMT

Please help me

RE: AUTOGENERATE Stored procedures example

Selom — Tue, 13 Apr 2010 06:01:59 GMT

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) outputasbeginset nocount on;declare @CurrentSID intdeclare @CurrentYear Varchar(4)declare @UniqueID varchar(8)declare @Lenght intdeclare @GeneratedID intdeclare @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_SIDvalues(@CurrentSID)delete from RefVal_SID where SID <>@CurrentSIDCOMMIT TRAN mytranset @Result = @UniqueIDend trybegin catchrollback tran mytranset @Result = 'Failed'end catchendI 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

AUTOGENERATE Stored procedures example

kodracon — Mon, 24 Nov 2008 21:40:29 GMT

Comments posted to this topic are about the item [B]AUTOGENERATE Stored procedures example[/B]