ALTER PROCEDURE [dbo].[Proc_BackUp_Delete]
@begintime nvarchar(14),
@endtime nvarchar(14),
@tablename nvarchar(50),
@fieldname nvarchar(50),
@datetype nvarchar(1)
AS
begin
declare @FileName varchar(50)
if @datetype ='1'
set @FileName=@tablename + '_'+SUBSTRING(@begintime,0,7)+'-'++SUBSTRING(@endtime,0,7)+'.txt'
if @datetype ='0'
set @FileName=@tablename + '_'+SUBSTRING(@begintime,0,8)+'-'++SUBSTRING(@endtime,0,9)+'.txt'
declare @BcpCmd varchar(500)
--1.利用BCP命令将该时间段的数据备份生成TXT数据存放在数据库服务器
-- EXEC master..xp_cmdshell 'BCP ''SELECT * FROM surf_rawdb.dbo.'+@tablename+' where '+@fieldname+' BETWEEN '+@begintime+' and '+@endtime+'' queryout e:\sqldataback\'+@FileName+' -t -c -U '"sa'" -P '"123456'"';
DECLARE @CMD NVARCHAR(4000)
SET @CMD='BCP "SELECT * FROM surf_rawdb.dbo.'+@tablename+' where '+@fieldname+' BETWEEN '''+@begintime+''' and '''+@endtime+'''" queryout e:\sqldataback\'+@filename+' -t -c -U "sa" -P "Sql2008r2"'
-- print @CMD
EXEC master..xp_cmdshell @CMD
--2.1声明国家站游标
declare tmp_Cursor cursor for select iiiii from info_station order by iiiii
--2.2声明区域站游标
declare tmp_Cursor_reg cursor for select iiiii from info_reg_station order by iiiii
declare @a varchar(10) set @a='REG'
DECLARE @Sql VARCHAR(500);
declare @stationid nvarchar(5)
declare @regstationid nvarchar(5)
if charindex(@a,UPPER(@tablename)) > 0
Begin
open tmp_Cursor_reg
while @@FETCH_STATUS =0
begin
fetch next from tmp_Cursor_reg into @regstationid
SET @Sql = 'delete from '+ @tablename +' where iiiii ='''+ @regstationid +'''and'+ @fieldname +' between '''+ @begintime+''' and '''+ @endtime+''''
PRINT @Sql;
EXECUTE (@Sql);
end
End
Else
Begin
open tmp_Cursor
print @tablename
print @@FETCH_STATUS
while @@FETCH_STATUS =0
begin
fetch next from tmp_Cursor into @stationid
SET @Sql = 'delete from '+ @tablename +' where iiiii ='''+ @stationid +'''and '+ @fieldname +' between '''+ @begintime+''' and '''+ @endtime+''''
PRINT @Sql;
EXECUTE (@Sql);
end
open tmp_Cursor_reg
while @@FETCH_STATUS =0
begin
fetch next from tmp_Cursor_reg into @regstationid
SET @Sql = 'delete from MC_CCCCFEEDBACK where iiiii ='''+ @regstationid +'''and'+ @fieldname +' between '''+ @begintime+''' and '''+ @endtime+''''
PRINT @Sql;
EXECUTE (@Sql);
end
End
close tmp_Cursor
deallocate tmp_Cursor
close tmp_Cursor_reg
deallocate tmp_Cursor_reg
End
上面写的存储过程，游标打开，关闭。
DECLARE @return_value int
EXEC @return_value = [dbo].[Proc_BackUp_Delete]
@begintime = N'20140801000000',
@endtime = N'20140930235959',
@tablename = N'SURF_reg_HOUR_DATA_BACKUP',
@fieldname = N'observtimes',
@datetype = N'1'
SELECT 'Return Value' = @return_value
调用时，第一次成功执行，第二次比如换别的表执行，就会提示游标未打开！！！
------解决思路----------------------
ALTER PROCEDURE [dbo].[Proc_BackUp_Delete]
@begintime NVARCHAR(14)
,@endtime NVARCHAR(14)
,@tablename NVARCHAR(50)
,@fieldname NVARCHAR(50)
,@datetype NVARCHAR(1)
AS
BEGIN
DECLARE @FileName VARCHAR(50)
IF @datetype = '1'
SET @FileName = @tablename + '_' + SUBSTRING(@begintime, 0, 7)
+ '-' + +SUBSTRING(@endtime, 0, 7) + '.txt'
IF @datetype = '0'
SET @FileName = @tablename + '_' + SUBSTRING(@begintime, 0, 8)
+ '-' + +SUBSTRING(@endtime, 0, 9) + '.txt'
DECLARE @BcpCmd VARCHAR(500)
--1.利用BCP命令将该时间段的数据备份生成TXT数据存放在数据库服务器
-- EXEC master..xp_cmdshell 'BCP ''SELECT * FROM surf_rawdb.dbo.'+@tablename+' where '+@fieldname+' BETWEEN '+@begintime+' and '+@endtime+'' queryout e:\sqldataback\'+@FileName+' -t -c -U '"sa'" -P '"123456'"';
DECLARE @CMD NVARCHAR(4000)
SET @CMD = 'BCP "SELECT * FROM surf_rawdb.dbo.' + @tablename
+ ' where ' + @fieldname + ' BETWEEN ''' + @begintime
+ ''' and ''' + @endtime + '''" queryout e:\sqldataback\'
+ @filename + ' -t -c -U "sa" -P "Sql2008r2"'
-- print @CMD
EXEC master..xp_cmdshell @CMD
DECLARE @a VARCHAR(10)
SET @a = 'REG'
DECLARE @Sql VARCHAR(500) ;
DECLARE @stationid NVARCHAR(5)
DECLARE @regstationid NVARCHAR(5)
IF CHARINDEX(@a, UPPER(@tablename)) > 0
BEGIN
--2.1声明国家站游标
DECLARE tmp_Cursor CURSOR FOR SELECT iiiii FROM info_station ORDER BY iiiii
OPEN tmp_Cursor_reg
FETCH NEXT FROM tmp_Cursor_reg INTO @regstationid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'delete from ' + @tablename
+ ' where iiiii =''' + @regstationid + '''and'
+ @fieldname + ' between ''' + @begintime
+ ''' and ''' + @endtime + ''''
PRINT @Sql ;
EXECUTE (@Sql) ;
FETCH NEXT FROM tmp_Cursor_reg INTO @regstationid
END
CLOSE tmp_Cursor
DEALLOCATE tmp_Cursor
END
ELSE
BEGIN
--2.2声明区域站游标
DECLARE tmp_Cursor_reg CURSOR FOR SELECT iiiii FROM info_reg_station ORDER BY iiiii
OPEN tmp_Cursor
FETCH NEXT FROM tmp_Cursor INTO @stationid
PRINT @tablename
PRINT @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'delete from ' + @tablename
+ ' where iiiii =''' + @stationid + '''and '
+ @fieldname + ' between ''' + @begintime
+ ''' and ''' + @endtime + ''''
PRINT @Sql ;
EXECUTE (@Sql) ;
FETCH NEXT FROM tmp_Cursor INTO @stationid
END
OPEN tmp_Cursor_reg
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'delete from MC_CCCCFEEDBACK where iiiii ='''
+ @regstationid + '''and' + @fieldname
+ ' between ''' + @begintime + ''' and '''
+ @endtime + ''''
PRINT @Sql ;
EXECUTE (@Sql) ;
FETCH NEXT FROM tmp_Cursor_reg INTO @regstationid
END
CLOSE tmp_Cursor_reg
DEALLOCATE tmp_Cursor_reg
END
END
用以上方法执行试试，db是否设置为本地游标，没有时最好指定