sql 2005关于存储过程的问题
create proc attach_db
as
begin
declare @dbname varchar(50),@i int,@dbPath varchar(50),@ldfPath varchar(50),@mdfPath varchar(50),@sql varchar(1000),@rq varchar(100)
declare mycs cursor FORWARD_ONLY for select distinct xzqh_bm from xzqh
open mycs
set @i = (select count(*) from (select distinct xzqh_bm from xzqh) as xzqh)
fetch next from mycs into @dbname
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @mdfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '.mdf'
set @ldfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '_log.ldf'
set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
exec (@sql)
while @i > 1
begin
fetch next from mycs into @dbname
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @mdfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '.mdf'
set @ldfPath='D:\zrzjk\CWJJJG' + @rq + '\' + @dbname + '_log.ldf'
set @sql='sp_attach_db ''' + @dbname +''', ''' + @mdfPath + ''' , ''' + @ldfPath + ''' '
exec (@sql)
set @i=@i-1
end
close mycs
DEALLOCATE mycs
end
这是创建的存储过程，执行的时候老是报错：消息 102，级别 15，状态 1，第 1 行
'371602' 附近有语法错误。'371602' 这个是xzqh 。
初学者，找不到原因，求解啊！！
[解决办法]
再试试，谁叫你用数字来做库名的首字符
create proc [dbo].[createDbTable]
as
begin
declare @rc nvarchar(100),@dbname varchar(100),@dblogname varchar(100),@mdfPath varchar(100),@ldfPath varchar(100),@createDbSql varchar(1000)
declare @i int,@j int,@dbPath varchar(100),@kjqj varchar(50),@gsdm varchar(50),@zth varchar(50),@kjnd varchar(50),@rq varchar(50),@pznrSql varchar(1000)
declare @pzmlSql varchar(1000),@fzxlbSql varchar(1000),@fzxzlSql varchar(1000),@kmxxSql varchar(1000),@yebSql varchar(1000),@ztcsSql varchar(1000),@pubkszlSql varchar(1000),@pubzyxxSql varchar(1000)
declare mycs cursor FORWARD_ONLY for select distinct xzqh_bm from xzqh
set @i =( select count(*) from xzqh )
set @j=@i
set @rq=CONVERT(VARCHAR(6),GETDATE(),112)-1
set @rc='md D:\zrzjk\CWJJJG'+ @rq
set @dbPath='D:\zrzjk\CWJJJG'+ @rq +'\'
exec xp_cmdshell @rc
open mycs
fetch next from mycs into @dbname
set @dblogname=QUOTENAME(@dbname+'_log')
set @mdfPath=QUOTENAME(@dbPath + @dbname + '.mdf')
set @ldfPath=QUOTENAME(@dbPath + @dbname + '_log.ldf')
set @kjqj=@rq
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @createDbSql=
'CREATE DATABASE ' + QUOTENAME(@dbname) + ' ON PRIMARY
( NAME= ''' + QUOTENAME(@dbname) + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + QUOTENAME(@dblogname) + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pznrSql)
set @pzmlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
/*此数据一般不做修改*/
set @fzxzlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
while @i>1
begin
fetch next from mycs into @dbname
set @kjqj=@rq
set @zth = right(@dbname,3)
set @gsdm=(select gsdm from xzqh where xzqh_bm=right(@dbname,10))
set @kjnd=CONVERT(VARCHAR(4),GETDATE(),112)
set @dblogname=QUOTENAME(@dbname+'_log')
set @mdfPath=QUOTENAME(@dbPath + @dbname + '.mdf')
set @ldfPath=QUOTENAME(@dbPath + @dbname + '_log.ldf')
set @createDbSql=
'CREATE DATABASE ' + @dbname + ' ON PRIMARY
( NAME= ''' + QUOTENAME(@dbname) + ''',FILENAME = ''' + @mdfPath + ''',SIZE=3072KB,FILEGROWTH=1024KB )
LOG ON
( NAME= ''' + QUOTENAME(@dblogname) + ''',FILENAME = ''' + @ldfPath + ''',SIZE=1024KB,FILEGROWTH=10%)'
exec(@createDbSql)
set @pznrSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pznr from caiwubz.dbo.gl_pznr where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec(@pznrSql)
set @pzmlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_pzml from caiwubz.dbo.gl_pzml where zth=''' + @zth + '''
and kjqj<=''' + @kjqj + ''' and gsdm=''' + @gsdm + ''' '
exec (@pzmlSql)
set @ztcsSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_ztcs from caiwubz.dbo.gl_ztcs where ztbh=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and hsdwdm=''' + @gsdm + ''' '
exec (@ztcsSql)
set @yebSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_yeb from caiwubz.dbo.gl_yeb where zth=''' + @zth + '''
and kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@yebSql)
set @fzxlbSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxlb from caiwubz.dbo.gl_fzxlb where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@fzxlbSql)
set @fzxzlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_fzxzl from caiwubz.dbo.gl_fzxzl where gsdm=''' + @gsdm + ''' '
exec (@fzxzlSql)
set @kmxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.gl_kmxx from caiwubz.dbo.gl_kmxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@kmxxSql)
set @pubkszlSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubkszl from caiwubz.dbo.pubkszl where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubkszlSql)
set @pubzyxxSql='select * into ' + QUOTENAME(@dbname) + '.dbo.pubzyxx from caiwubz.dbo.pubzyxx where kjnd<=''' + @kjnd + ''' and gsdm=''' + @gsdm + ''' '
exec (@pubzyxxSql)
set @i=@i-1
end
close mycs
DEALLOCATE mycs
end
