对数据库自动添加文件存储过程up_add_file

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[up_add_file]
(
	@filemaxsize int,
	@fileminsize int,
	@filepath nvarchar(255),
	@groupid int,
	@groupname nvarchar(20)
)
as
declare @size int;
declare	@maxsize int;
declare	@filescount int;
declare	@name nvarchar(255);
declare	@filename nvarchar(255);
declare @fileexiststate int;
declare @fstimes int;
begin
	print '[up_add_file]'
	
	/*获取该组最新文件大小--1048578*/
	select top 1 @size=size/128,@maxsize=maxsize/128 from sysfiles where status=1048578 and groupid=@groupid and maxsize>0 order by fileid desc
	if(@size is null)goto toexit;
	print '@size:'+convert(varchar(10),@size)
	print '@maxsize:'+convert(varchar(10),@maxsize)
	print @maxsize-@size
	print @fileminsize

	if(@maxsize-@size>@fileminsize)goto toexit;
	
	/*获取该文件数量*/
	select @filescount=count(fileid) from sysfiles where status=1048578 and groupid=@groupid and maxsize>0
	print '@filescount:'+convert(varchar(10),@filescount)
	set @filescount=@filescount+1
	set @name='dbtrade2012'+@groupname+'_'+convert(varchar(10),@filescount);
	print '@name:'+@name
	set @filename=@filepath+'dbtrade2012'+'_'+convert(varchar(10),@filescount)+'_'+@groupname+'.ndf'
	print '@filename:'+@filename
	
	set @fstimes=1

checkfileexiststate:
	exec   master..xp_fileexist  @filename, @fileexiststate  output
	if(@fileexiststate=1)
	begin
		set @name=@name+'_ex'+convert(varchar(10),@fstimes);
		set @filename=@filepath+'dbtrade2012'+'_'+convert(varchar(10),@filescount)+'_'+@groupname+'_ex'+convert(varchar(10),@fstimes)+'.ndf';
		set @fstimes=@fstimes+1;
		goto checkfileexiststate;
	end

	print '@name:'+@name;
	print '@filename:'+@filename;
	print '创建文件：'+@name
	execute ('
	alter database dbtrade2012 
	add file 
	(
		name = '''+@name+''',
		filename ='''+ @filename+''',
		size = 1mb,
		maxsize = '+@filemaxsize+'mb,
		filegrowth = 10%
	)
	to filegroup ['+@groupname+']'
	);
	toexit:
		print '------exit------'
end