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

• royston_pledger

  Ten Centuries

  Points: 1031

  More actions

  May 26, 2008 at 5:32 am

  #190998

  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

• royston_pledger

  Ten Centuries

  Points: 1031

  More actions

  May 27, 2008 at 3:25 am

  #820419

  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: