需要有关此sql Server 2000过程的帮助.问题变得困难,因为我正在通过Oracle sql Developer测试程序.
我正在运行程序,使用Varchar格式的新数字序列迭代列,以获得具有空值的人.
但我一直收到错误,所以a)我可能做错了方法b)由于使用的版本语法不正确.我主要是Oracle用户.
我一直得到错误：sql错误：关键字“结束”附近的语法不正确.这没有足够的帮助解决它.结束指的是程序中最后一个’结束’.
任何帮助将不胜感激.
这是程序.
ALTER PROCEDURE [dbo].[OF_AUTOSEQUENCE] @JvarTable Varchar(250),@varColumn Varchar(250),@optIsString char(1),@optInterval int AS
/*
Procedure OF_AUTOSEQUENCE
Created by Joshua [Surname omitted]
When 20100902
Purpose To fill up column with new sequence numbers
Arguments varTable - Table name
varColumn - Column name
optIsString - Option: is it string or numeric,either use T(rue) or F(alse)
optInterval - Steps in increment in building new sequence (Should be 1 (one))
Example script to begin procedure
EXECUTE [dbo].[OF_AUTOSEQUENCE] 'dbo.EH_BrownBin','Match','T',1
Any questions about this,please send email to
[business email omitted]
*/
declare
@topseed int,@stg_topseed varchar(100),@sql_string nvarchar(4000),@myERROR int,@myRowCount int
set @sql_string = 'Declare MyCur CURSOR FOR select ' + @varColumn + ' from ' + @JvarTable + ' where ' + @varColumn + ' is null'
Exec sp_executesql @sql_string
SET NOCOUNT ON
Begin
if @optIsString = 'T'
Begin
set @sql_string = 'select top 1 ' + @varColumn + ' from ' + @JvarTable + ' order by convert(int,' + @varColumn + ') desc'
set @stg_topseed = @sql_string
set @topseed = convert(int,@stg_topseed)
ENd
else
Begin
set @sql_string = 'select top 1 ' + @varColumn + ' from ' + @JvarTable + ' order by ' + @varColumn + ' desc'
set @topseed = @sql_string
ENd
-- SELECT @myERROR = @@ERROR,@myRowCOUNT = @@ROWCOUNT
-- IF @myERROR != 0 GOTO HANDLE_ERROR
open MyCur
fetch next from MyCur
WHILE @@FETCH_STATUS = 0
set @topseed = @topseed + @optInterval
if @optIsString = 'T'
begin
set @sql_string = 'update ' + @JvarTable + ' set ' + @varColumn + ' = cast((' + @topseed + ') as char) where current of ' + MyCur
exec (@sql_string)
ENd
else
begin
set @sql_string = 'update ' + @JvarTable + ' set ' + @varColumn + ' = ' + @topseed + ' where current of ' + MyCur
exec (@sql_string)
ENd
fetch next from MyCur
ENd
-- SELECT @myERROR = @@ERROR,@myRowCOUNT = @@ROWCOUNT
-- IF @myERROR != 0 GOTO HANDLE_ERROR
--HANDLE_ERROR:
--print @myERROR
CLOSE MyCur
DEALLOCATE MyCur
End
解决方法
你在WHILE之后就错过了一个开头.你缩进就像你想要一个块(多个语句)在while循环中,甚至有一个结束,但没有开始.
做了：
...
open MyCur
fetch next from MyCur
WHILE @@FETCH_STATUS = 0
begin --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<add this
set @topseed = @topseed + @optInterval
if @optIsString = 'T'
begin
set @sql_string = 'update ' + @JvarTable + ' set ' + @varColumn + ' = cast((' + @topseed + ') as char) where current of ' + MyCur
exec (@sql_string)
ENd
else
begin
set @sql_string = 'update ' + @JvarTable + ' set ' + @varColumn + ' = ' + @topseed + ' where current of ' + MyCur
exec (@sql_string)
ENd
fetch next from MyCur
ENd
...
