在sql procdure中生成recipt [英] generate recipt in sql procdure

问题描述

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,.........

推荐答案

之所以返回起始值是因为条件

The reason why it is returning starting value because the condition

if(@FeesYear=YEAR(GETDATE()) and @FeesMonth=MONTH(@FeesMonth))

返回false。检查变量中的值。

is returning false. Check the values in the variable.

这篇关于在sql procdure中生成recipt的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！