Issues running a stored procedure through SQL script, used to work until I changed one of the parameter from Varchar to Int

Question

I hope somebody can help me here, I have a table that has the name of the Stored Procedure that I would like to run, it also has a parameter that I would like to pass through into the Stored procedure.

declare @RowCnt int
declare @MaxRows int
declare @ExecSql nvarchar(255)

select @RowCnt = 1

These next two rows are specific to source table or query

I create a temporary table in that just has the information that i require

declare @Import table (
rownum int IDENTITY (1, 1) 
Primary key NOT NULL , 
LOB_ID int, 
LOB_Level varchar(25), 
LOB_WS varchar(50)
)

insert into @Import (LOB_ID,LOB_Level,LOB_WS) 

SELECT     LOB_ID, LOB_BODLevel, LOB_Parameters
FROM         view_LOB_Update_Mappings

select @MaxRows=count(*) from @Import

Now I want to run through the temporary table passing through the variables of stored procedure name LOB_Level

LOB_ID -> this used to be a Varchar, but I had to change and ever since this code no longer works

LOB_WS - Which is the 2nd parameter

while @RowCnt <= @MaxRows
begin

    select @ExecSql = 'exec ' + LOB_Level + ' ' + LOB_ID + ',' +  ' ''' + LOB_WS + '''' from @Import where rownum = @RowCnt 
    execute sp_executesql @ExecSql

   Select @RowCnt = @RowCnt + 1

end

The error message that I get is:

Msg 245, Level 16, State 1, Line 25
Syntax error converting the varchar value 'exec Update_LOB_Mappings_L35 ' to a column of data type int.

Any help that you can provide would be greatly appreciated!

John

Answer 1

I think you need to convert LOB_ID to a string before you try to use it at line 25. In mysql this could be CONVERT(LOB_ID, CHAR(8))