Auto Generate RefNo in SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Auto Generate RefNo in SP

Author

Topic

krushnapanda
Starting Member

18 Posts

Posted - 2007-11-14 : 00:11:55

Hello Dear ,
Please Solve my problem its Urgent.
My Table is tblGenerateCode
Fields are : SlNo,RefNo,Branch,Mode,TranDate

SlNo is Identity = True column
Mode is 2 types DD and TT
RefNo is Generate Like this : Rf_SlNo/Branch/Mode/Month/Year

Example : lets SP is sp_GenerateNo
then execute the SP
Exec sp_GenerateNo 1,'','','MUMBAI,'DD','10-September-2007'

I am passing only Branch,Mode,TranDate
SlNo is Identity , RefNo is Generate automaticalyy like this
1/MUMBAI/DD/September/2007
next record 2/MUMBAI/DD/September/2007
RefNo is generate
as per Mode ,as per Month , as per Branch

How to generate this in SQL Server 2005 using SP i was done it
The SP is
Alter proc sp_GenerateNo
(
@Flag as int,
@slno1 as int=null,
@Branch as varchar(50),
@RefNo as varchar(50)= null,
@TranDate as datetime,
@mode as varchar(50)
)
as
if @Flag = 1 -- Insert
Begin
Declare @Count44 As Numeric
Select @Count44 = count(*) from tblGenerateCode
Where @RefNo Like '%' + @Branch + '/' + @mode + '/' + DATENAME(month, @TranDate) + '%'
Set @RefNo = cast(@Count44 + 1 as varchar) + '/' + @Branch + '/' + @mode + '/' + DATENAME(month, @TranDate)
+ '/' + convert(varchar(10),year(@TranDate))
Set @slno = cast(@Count44 + 2 as varchar) + '/' + @Branch + '/' + @mode + '/' + DATENAME(month, @TranDate)
+ '/' + convert(varchar(10),year(@TranDate))
Insert into tblGenerateCode values(@RefNo ,@Branch,@mode,@TranDate)
End
else if @Flag = 2 -- Update
Begin

End

Please write me how to handel this at the time of updateWhen update the condition are
if i update only the Date of the partcular transaction
if i update only the month of the partcular transaction
if i update only the Mode of the partcular transaction
if i update the Month again and again of the partcular transaction

Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-14 : 00:29:58

You're data table structure does not follow very good practices. Please post your table structure, and what you are looking to do. Someone can probably point you in a better direction, unfortuantly the procedure you want to make work is likley just going to cause significant issues the larger your db grows.

A basic update statement is this


Update a
set a.MyColumn = @MyValue
from 
Mytable a
where 
a.MyColumn = @MyCriteria

but I don't think that's your issue.

krushnapanda
Starting Member

18 Posts

Posted - 2007-11-14 : 02:39:44

My Table is tblGenerateCode
Fields are : SlNo,RefNo,Branch,Mode,TranDate
SlNo is Identity = True column
Mode is 2 types DD and TT
RefNo is Generate Like this : Rf_SlNo/Branch/Mode/Month/Year
example : when i pass Branch = 'Mumbai' , Mode ='DD' , TranDate = '10-Sep-2007'
then the RefNo is look like this : 1/Mumbai/DD/September/2007
is the Firset record, when i insert 2nd record for same branch ,same mode,same month
then the refno is look like this : 2/Mumbai/DD/September/2007
This Rf_SlNo is generate base on Branch , Mode , Month
Now plz write an SP for Insert and Update
For Insert its ok but at the time of updation i face this Problems
say i want to update this 2/Mumbai/DD/September/2007 records
If i update the Month Sep to August
then it should be count all RefNo from August then generate the Next For August
suppose in August total DD in Mumbai are 4 , then the updation records is like this
5/Mumbai/DD/August/2007
condition for Update records are :
if i update only the Date of the partcular transaction the RefNo should not b change
if i update only the month of the partcular transaction the RefNo change acording To Month
if i update only the Mode of the partcular transaction the RefNo change
if i update the Month again and again of the partcular transaction the RefNo change acordingly

still , if you not getting me then repl me
thanks

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources