SQL错误:关键字“结束”附近的语法不正确

时间:2022-03-27 01:07:59

Need help with this SQL Server 2000 procedure. The problem is made difficult because I'm testing procedure via Oracle SQL Developer.

需要有关此SQL Server 2000过程的帮助。问题变得困难,因为我正在通过Oracle SQL Developer测试程序。

I'm running the procedure to iterate column with new sequence of numbers in Varchar format for those who have null values.

我正在运行程序,使用Varchar格式的新数字序列迭代列,以获得具有空值的人。

But I keep getting error, so a) I may have done a wrong approach b) syntax is incorrect due to version used. I'm primarily Oracle user.

但我一直收到错误,所以a)我可能做错了方法b)由于使用的版本语法不正确。我主要是Oracle用户。

Error I keep getting: SQL Error: Incorrect syntax near the keyword 'End'. which isn't helpful enough to fix it out. The End refers to the very last 'End' in the procedure.

我一直得到错误:SQL错误:关键字“结束”附近的语法不正确。这没有足够的帮助解决它。结束指的是程序中最后一个'结束'。

Any help would be greatly appreciated.

任何帮助将不胜感激。

Here's the Procedure.

这是程序。

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

1 个解决方案

#1


8  

you're missing a begin right after the WHILE. You indented like you want a block (multiple statements) in the while loop, and even have a end for the while, but no begin.

你在WHILE之后就错过了一个开头。你缩进就像你想要一个块(多个语句)在while循环中,甚至有一个结束,但没有开始。

make it:

做了:

...
  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
...

#1


8  

you're missing a begin right after the WHILE. You indented like you want a block (multiple statements) in the while loop, and even have a end for the while, but no begin.

你在WHILE之后就错过了一个开头。你缩进就像你想要一个块(多个语句)在while循环中,甚至有一个结束,但没有开始。

make it:

做了:

...
  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
...