动态游标

CREATE PROCEDURE WGetMaxNo @TableName varchar(20),@FieldName varchar(20)  AS
declare @MaxNo varchar(10)
declare @aa int
declare @bb varchar(10)
declare @sql nvarchar(500)
              
set @sql='declare WMaxNo_Cursor Cursor for select max(convert(int,'+@FieldName+'))+1 as MyData  from  '+@TableName+' Where ISNUMERIC('+@FieldName+')=1'
exec ( @sql)
open WMaxNo_Cursor
fetch next from WMaxNo_Cursor into @aa
if @@Fetch_status=0
  if @aa is null
   select @maxno='0000000001'
  else
   begin
    select @bb=convert(varchar(10),@aa)
    select @MaxNo=substring('0000000000',1,10-len(@bb))+@bb
   end
else
 select @MaxNo='0000000001'
close WMaxNo_Cursor
Deallocate WMaxNo_Cursor
select @Maxno
GO