calling a stored procedure(with no return parameters) from jdbc

  • Hi,

    I have a stored procedure that requires two input parameters and no return parameter.. The proc is called from java...as

    CallableStatement csmt = null;

    csmt = con.prepareCall("{call dbo.MSsqlCommonProc(?,?)}");

    csmt.setString(1,tableName);

    csmt.setString(2,outputTableName);

    int result = csmt.executeUpdate();

    I get the following error when the proc is called...

    [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near '1'.]

    I dont have an idea as to what this error means or is the call statement correct...

    Need help on this..

    thanks

    Roy

  • Hi,

    i got this resolved. The problem was due to an invalid statement in the stored procedure...the error did not appear when the stored procedure was compiled..but when called from a java program

    ---------------------------------------------------------------------

    The code:

    CREATE PROC myProcedure @tableA_name varchar(30),@tableA_column varchar(30)

    AS

    BEGIN

    DECLARE @param1 varchar(50),

    @param2 varchar(50),

    @param3 varchar(50),

    @functionString varchar(1000),

    @updateQuery varchar(1000)

    SET @updateQuery = 'update '+@tableA_name+' set '

    SET @param1 = 'tableB';

    SET @param2 = 'STATE';

    SET @param3 = '';

    -- this EXEC statement caused the error

    EXEC ('insert into tableC(tab_Name,colm_Name) values ('''+@tablename+''','''+@col+''')');

    -- the corrected statement is

    -- insert into tableC(tab_Name,colm_Name) values(@tablename,@col)

    SET @functionString = '(myFunction('''+@param1+''','''+@param2+''','''+@param3+''')),';

    SET @updateQuery = @updateQuery + @tableA_column +'='+@functionString;

    EXECUTE(@updateQueryFinal);

    END

    --------------------------------------------------------------------

    It was my mistake of not providing the procedure code too...:hehe:

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply