/*過程名 getNum功能:  取得指定表的下一個可用的編號輸入參數:   @tablName 表名@FileldName 即要查詢的字段名.在不同的表編號所寫的列名不是一樣的.如有ID Num等@Num 返回下一個可用的編號  */CREATE PROCEDURE dbo.getNum  @tablName VARCHAR(8),@FileldName VARCHAR(8),@Num  VARCHAR(6) OUTPUTASDECLARE @Sql  VARCHAR(200)DECLARE @oldNum INT,@newNum VARCHAR(6)SET @Sql=''  SET @Sql='DECLARE Mycursor CURSOR FOR   SELECT '+ RTRIM(@FileldName) + ' FROM ' + RTRIM(@tablName) EXEC(@Sql)IF @@ERROR=0BEGINSET  @oldNum=1OPEN MycursorFETCH NEXT FROM Mycursor INTO @newNumWHILE @@FETCH_STATUS=0BEGINIF REPLICATE(0,3-LEN(@oldNum))+CAST(@oldNum AS VARCHAR) = @newNum SET @oldNum=@oldNum+1ELSEBEGIN SET @Num=REPLICATE(0,3-LEN(@oldNum))+CAST(@oldNum AS VARCHAR) RETURN  ENDFETCH NEXT FROM Mycursor INTO @newNum ENDCLOSE MycursorDEALLOCATE MycursorSET @Num=REPLICATE(0,3-LEN(@oldNum))+CAST(@oldNum AS VARCHAR) --如果順序沒有被打斷RETURNEND   ELSERAISERROR('該表或列名不存在.請檢查輸入的表名和列名是否正確?',7,1)GO