Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


AUTOGENERATE Stored procedures example


AUTOGENERATE Stored procedures example

Author
Message
kodracon
kodracon
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 162
Comments posted to this topic are about the item AUTOGENERATE Stored procedures example
Selom
Selom
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Selom
Selom
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
Please help me
Selom
Selom
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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. ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search