SQL Error: Incorrect syntax near the keyword 'End'
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
.
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
...
Related videos on Youtube
Joshua
Working for a dual Council - Vale of White Horse District Council and South Oxfordshire District Council. Principal Developer on online forms, with back-end systems. Oracle Database infrastructure with Online Forms from Ebase Technology Ltd. Also work with ESRI ArcIMS for GIS solutions. Backend system - Ocella, also uses Oracle Reports 6i.
Updated on September 03, 2020Comments
-
Joshua over 3 years
Need help with this SQL Server 2000 procedure. The problem is made difficult because I'm testing procedure via 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.
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.
Error I keep getting:
SQL Error: Incorrect syntax near the keyword 'End'.
which isn't helpful enough to fix it out. TheEnd
refers to the very last 'End' in the procedure.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
-
Joshua over 13 yearsWell spotted. Now I have a different error. <code>SQL Error: Invalid column name 'MyCur'.</code>. Maybe my approach is wrong.
-
KM. over 13 yearsyou need to
FETCH NEXT FROM MyCur INTO @a
. so, you must declare a local variable @a previously and then then change ...+ ' where current of ' + MyCur
to ...+ ' where current of ' + @a
-
Zarepheth over 5 yearsThe missing
BEGIN
statement could be anywhere in the code prior to the error. I found one missing after a nested if in my own code. Never the less, this answer suggested what to look for.