SQL Server: Error in Cursorless stored procedure

Question

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 = ''' + @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

Answer 1

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