Alter Proc GenrateFeesRecieptCode As declare @dt1 nvarchar(12) declare @strYear nvarchar(6) declare @CurrMonth nvarchar(3) declare @TempID nvarchar(5) declare @lenMonth nvarchar(5) declare @Rec_C nvarchar(15) declare @CurrDate date declare @FeesYear nvarchar(10) declare @FeesMonth nvarchar(10) declare @FeesCode nvarchar(10) declare @Mn nvarchar(10) declare @Rshow nvarchar(5) declare @ReceiptNo nvarchar(10) set @CurrDate='5-Jun-2014' SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN if(MONTH(@CurrDate)>3) set @strYear=year(@CurrDate) else set @strYear=year(@CurrDate)-1 set @dt1='1-Apr-'+ @strYear --select @strYear --select @dt1 set @lenMonth=MONTH(GETDATE()) if len(@lenMonth)=1 set @CurrMonth='0'+@lenMonth --select @CurrMonth if exists(SELECT * From S_Fees_Mst WHERE Fees_Code<> '' and Date_Added >= @dt1) begin SELECT @FeesYear=FeesYear, @FeesMonth=FeesMonth, @FeesCode=Fees_Code From S_Fees_Mst WHERE Fees_Code<> '' and Date_Added >= @dt1 ORDER BY FeesYear desc,FeesMonth desc,Fees_Code desc if(@FeesYear=YEAR(GETDATE()) and @FeesMonth=MONTH(@FeesMonth)) begin set @Mn=SUBSTRING(@FeesCode, LEN(@FeesCode)-2,len(@FeesCode)) set @TempID=@Mn+1 if len(@TempID)=1 set @ReceiptNo='R-'+@CurrMonth+'/00'+@TempID else if len(@TempID)=2 set @ReceiptNo='R-'+@CurrMonth+'/0'+@TempID else set @ReceiptNo='R-'+@CurrMonth+'/'+@TempID end else set @ReceiptNo='R-'+@CurrMonth+'/001' end else set @ReceiptNo='R-'+@CurrMonth+'/001' select @ReceiptNo as receiptno commit it only give static valu like R-05/001 where R=receipt name 05 is month name and 001 is serial no statrt from problem is i want generate sequence no like R-05/001,R-05/002,R-05/002,.........
if(@FeesYear=YEAR(GETDATE()) and @FeesMonth=MONTH(@FeesMonth))
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)