SQL Server: Error in Cursorless stored procedure

I'm having trouble with this stored procedure, could you please help.

This is error I'm getting - running all this via Oracle Sql developer on SQL Server 2000 hosted elsewhere.

Error

Error starting at line 1 in command:

execute dbo.OF_ASEQ_EH_BROWNBIN 'dbo.EH_Brownbin_Josh','Match', 1

Error report:

Incorrect syntax near the keyword 'BEGIN'.

Procedure

ALTER PROCEDURE [dbo].[OF_ASEQ_EH_BROWNBIN] 
@JvarTable Varchar(250), 
@varColumn Varchar(250), 
@optInterval int 
AS


declare   @Sql_string   nvarchar(4000)  
declare   @myERROR      int  
declare   @myRowCount   int  



declare   @topseed      int  
declare   @stg_topseed  varchar(100)  


-- Temp table for rows with nulls in specific column


declare   @RowCnt       int  
declare   @MaxRows      int  
declare   @col_Name     nvarchar(250)  
declare   @col_UPRN     nvarchar(250)  
declare   @col_JoinedOn smalldatetime  

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)  



SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT  
IF @myERROR != 0 GOTO HANDLE_ERROR  



select @RowCnt = 1  

declare @Import table  
(  
rownum int IDENTITY (1, 1) Primary key NOT NULL ,  
col_Name nvarchar(250),  
col_UPRN nvarchar(250),  
col_JoinedOn smalldatetime  
)  

set @sql_string = 'insert into @Import (col_Name, col_UPRN, col_JoinedOn) select Name, UPRN, JoinedOn from ' + @JvarTable + ' where ' + @varColumn +' is null'
exec @Sql_string

SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT  
IF @myERROR != 0 GOTO HANDLE_ERROR  

select @MaxRows=count(*) from @Import  

SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT  
IF @myERROR != 0 GOTO HANDLE_ERROR

-- Next new seed  
select @topseed = @topseed + @optInterval
<br/>
while @RowCnt <= @MaxRows  
  begin   
    select @col_Name = col_Name from @Import where rownum = @RowCnt  
    select @col_UPRN = col_UPRN from @Import where rownum = @RowCnt  
    select @col_JoinedOn = col_JoinedOn from @Import where rownum = @RowCnt  

    set @Sql_string = 'update ' + @JvarTable + ' set ' + @varColumn + ' = cast((' + @topseed + ') as char) where Name = ''开发者_StackOverflow社区' + @col_Name + ''' and UPRN = ''' + @col_UPRN + ''' and JoinedOn = ''' + @col_JoinedOn + ''' '  
    exec (@Sql_string)  

    select @topseed = @topseed + @optInterval  
    Select @RowCnt = @RowCnt + 1  
  end

SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT  
IF @myERROR != 0 GOTO HANDLE_ERROR

HANDLE_ERROR:  
RETURN @myERROR  

end

Whereas in Oracle, you need to have DECLARE - BEGIN - END, in MSSQL you do not have to use the BEGIN/END keywords on the procedure body.

Note that the label HANDLE_ERROR: is outside the BEGIN/END.

My guess is that removing the BEGIN (after the DECLARE block) and the END (before SELECT @myERROR=...), the error goes away.

Edit:

I guess I don't get the following statement:

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) 

You assign a string var, copy the value to another string var, and, instead of executing the SQL statement, you cast the string var into an int. (?)

There were several errors in that code and there's probably a better, set based, way to solve the problem this stored procedure is for. (But that's another question and requires knowledge of the target tables that was not provided.)

The following code should be a step closer to what you're looking for.
Changes:

1. Edited suspicious code, cast((' + @topseed + ') as char) is that column really char? or is it varchar(something)?
2. Corrected logic around @topseed fetch.
3. Corrected logic around Import table use.
4. Corrected update SQL generation.
5. Removed extraneous Begin/ENDs.
6. Removed extraneous HTML (<br/>).
7. Wrapped procedure with Begin/END.
8. Added Go at the end.

Code:

ALTER PROCEDURE [dbo].[OF_ASEQ_EH_BROWNBIN]
    @JvarTable Varchar(250),
    @varColumn Varchar(250),
    @optInterval int
AS
BEGIN
    DECLARE   @Sql_string   nvarchar(4000)
    DECLARE   @myERROR      int
    DECLARE   @myRowCount   int
    DECLARE   @topseed      int
    DECLARE   @stg_topseed  varchar(100)

    -- Temp table for rows with nulls in specific column
    DECLARE   @RowCnt       int
    DECLARE   @MaxRows      int
    DECLARE   @col_Name     nvarchar(250)
    DECLARE   @col_UPRN     nvarchar(250)
    DECLARE   @col_JoinedOn smalldatetime

    SET @Sql_string     = 'select top 1 @stg_topseed = ' + @varColumn + ' from ' + @JvarTable + ' order by convert(int, ' + @varColumn + ') desc'
    EXEC SP_EXECUTESQL @Sql_string, N'@stg_topseed varchar(100) OUTPUT', @stg_topseed OUTPUT

    SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

    SET @topseed        = CONVERT(int, @stg_topseed)

    /* Can't use a table variable with EXEC and/or SP_EXECUTESQL.
        Therefore, forced to use a temporary table.
    */
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('#Import') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        DROP TABLE #Import

    CREATE table #Import
    (
        rownum          int IDENTITY (1, 1) Primary key NOT NULL,
        col_Name        nvarchar(250),
        col_UPRN        nvarchar(250),
        col_JoinedOn    smalldatetime
    )

    SET  @sql_string = 'insert into #Import (col_Name, col_UPRN, col_JoinedOn) select Name, UPRN, JoinedOn from ' + @JvarTable + ' where ' + @varColumn +' is null'
    EXEC (@Sql_string)

    SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

    SELECT @MaxRows=count(*) from #Import

    SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

    -- Next new seed
    SELECT @topseed = @topseed + @optInterval
    SELECT @RowCnt = 1

    WHILE @RowCnt <= @MaxRows
      BEGIN
        SELECT @col_Name = col_Name from #Import where rownum = @RowCnt
        SELECT @col_UPRN = col_UPRN from #Import where rownum = @RowCnt
        SELECT @col_JoinedOn = col_JoinedOn from #Import where rownum = @RowCnt

        SET    @Sql_string = 'update ' + @JvarTable + ' set ' + @varColumn + ' = ''' + CAST (@topseed AS varchar(250)) + '''  where Name = ''' + @col_Name + ''' and UPRN = ''' + @col_UPRN + ''' and JoinedOn = ''' + CAST(@col_JoinedOn AS varchar(250)) + ''' '
        EXEC  (@Sql_string)

        SELECT @topseed = @topseed + @optInterval
        SELECT @RowCnt = @RowCnt + 1
      END

    SELECT  @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT
    IF      @myERROR != 0 GOTO HANDLE_ERROR

    HANDLE_ERROR:
    RETURN @myERROR
END
GO

I've been doing housekeeping on StackOverflow, and realised I hadn't (or accepted) answered the question I asked a long time ago. I couldn't remember which method/work-around I decided on, but I felt it's important to upload my accepted and working script, although I no longer need it.

Nevertheless, I sincerely hope this code will help the next person who has similar question to mine.

CREATE PROCEDURE [dbo].[OF_ASEQ_EH_BROWNBIN] @JvarTable nvarchar(250), @varColumn nvarchar(250), @optInterval int AS
/*
Procedure   OF_ASEQ_EH_BROWNBIN
Created by  Joshua White
When        20100902

Purpose     To fill up column with new sequence numbers
Arguments   JvarTable   - Table name
            varColumn   - Column name
            optInterval - Steps in increment in building new sequence (Should be 1 (one))

Example script to begin procedure

EXECUTE [dbo].[OF_ASEQ_EH_BROWNBIN] 'EH_Brownbin_Josh', 'Match', 1

PLEASE NOTE - Typically Stored Procedures are supposed to be flexible to work on
any tables, but due to complications with SQL Server 2000 and problems with
Cursors, we have to use manual scripts and this Stored Procedure will only work
on EH_BrownBin table ONLY.

Any questions about this, please send email to
<email deleted>
*/

declare   @Sql_string   nvarchar(4000)
declare   @myERROR      int    
declare   @myRowCount   int

/* Fetching the last number in rows of table in question */
declare   @topseed      int
declare   @stg_topseed  varchar(100)

-- Temp table for rows with nulls in specific column
declare   @RowCnt       int
declare   @MaxRows      int
declare   @rc           int 
declare   @colu_Name     nvarchar(250)
declare   @colu_UPRN     nvarchar(250)
declare   @colu_JoinedOn smalldatetime

Begin
  set @stg_topseed = 'select top 1 ' + @varColumn + ' from ' + @JvarTable + ' order by convert(int, ' + @varColumn + ') desc'
  exec (@stg_topseed)

/* Begin collecting all rows with nulls in specified column */
select @RowCnt = 1

declare @Import table
(
  rownum int IDENTITY (1, 1) Primary key NOT NULL ,
  colu_Name nvarchar(250),
  colu_UPRN nvarchar(250),
  colu_JoinedOn smalldatetime
) 
set nocount on
select @MaxRows = count(*) from @Import

-- Next new seed
select @stg_topseed = @stg_topseed + @optInterval
select @rc=0 

while @RowCnt <= @MaxRows
  begin 
    select @colu_Name = colu_Name from @Import where rownum = @RowCnt
    select @colu_UPRN = colu_UPRN from @Import where rownum = @RowCnt
    select @colu_JoinedOn = colu_JoinedOn from @Import where rownum = @RowCnt

    set @Sql_string = 'update ' + @JvarTable + ' set ' + @varColumn + ' = ' + @stg_topseed + ' where Name = ''' + @colu_Name + ''' and UPRN = ''' + @colu_UPRN + ''' '
    exec (@Sql_string)
    select @stg_topseed = @stg_topseed + @optInterval
    Select @RowCnt = @RowCnt + 1 
  end

print 'END'
end