CreateProc GetICBillNo @billtypeint, @isusebit, @billnovarchar(100) out as set@billno='' select a.*,isnull(b.ftable,'') as ftable,isnull(e.ffieldname,'') as FieldName into #tmp from t_billcoderule a leftjoin t_option e on a.fprojectid=e.fprojectid and a.fformatindex=e.fid LeftOUterjoin t_checkproject b on a.fbilltype=b.fbilltypeid and a.fprojectval=b.ffield where a.fbilltypeid =@billtypeorderby a.FClassIndex declare@pidint,@pvalvarchar(20),@lengthint,@formatvarchar(20) DECLARE cc CURSORFOR SELECT FProjectID, FProjectVal, FLength,FFormatIndex FROM #tmp ORDERBY FClassIndex OPEN cc FETCHNEXTFROM cc INTO@pid,@pval,@length,@format WHILE@@FETCH_STATUS=0 BEGIN if(@pid=1) begin set@billno=@billno+@pval end elseif(@pid=3) begin declare@vvarchar(100) set@v='00000000000000000000000000000000000000000000000000000000' set@billno=@billno+left(@v,@length-len(@pval))+@pval end FETCHNEXTFROM cc INTO@pid,@pval,@length,@format END CLOSE cc DEALLOCATE cc if(@isuse=1) Update t_billcoderule set FProjectVal=Cast(FProjectVal asint)+1where fbilltypeid =@billtypeand FProjectID=3 select@billno GO declare@novarchar(100) Exec GetICBillNo 80,0,@no out print@no