SQL Server: Error in Cursorless stored procedure

Question

Hi

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

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. (?)

Answer 2

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