Hi Witold !
You should not use the RETURN statement in your stored procedure. Use SELECT <your_value> to return what you want to fetch in you java code. RETURN is used to exit your statement (unconditionally). Try this instead: ---------------------------------------------------- CREATE PROCEDURE sp_BHT ( @var1 varchar(32), @var2 varchar(32), @var3 varchar(32), @var4 varchar(32) output) AS -- -- some other stuff here -- -- set your OUTPUT -variabel value select @var4 = '<output_variabel_value>' -- set the value to be returned from the stored proc select '<return_value>' return 0 ---------------------------------------------------- Hope it helps... /stefan -----Ursprungligt meddelande----- Fr�n: Witold Iwaniec [mailto:[EMAIL PROTECTED]] Skickat: den 10 juni 2002 16:13 Till: JDJList �mne: [jdjlist] RE: SQL Server and stored procedure Naveen Thanks for the reply. It works but it uses an OUT parameter, which is the fourth parameter passed to the procedure. I am looking for a return value which is not out parameter. According to the on-line help it would be something like: csmt = con.prepareCall("{? = Call sp_BHT(?, ?, ?, ?)}"); you still would register it as out parameter. It works with Oracle but SQL Server driver doesn't like this, or few other variations of, syntax Witold On 8 Jun 2002 at 11:04, Naveen Joshi wrote: > Hi Iwaniec, > > Try this. It works for me. > > Connection con = null; > CallableStatement csmt = null; > try{ > //specify the stored procedure to execute > csmt = con.prepareCall("{Call sp_BHT(?, ?, ?, ?)}"); > > //set input and output parameters if any > csmt.setString(1,strEdi_tran_no); > csmt.setString(2,strTrans_set_cde); > csmt.setString(3,strHd_tl_id); > csmt.registerOutParameter(4,Types.VARCHAR); > > csmt.execute(); > > //get the output parameters if any > strBHT = csmt.getString(4); > > csmt.close(); > > }catch(Exception e){ > out.println("Exception :"+e); > } > > > Thanks > Naveen > > ---------- > From: Witold Iwaniec [SMTP:[EMAIL PROTECTED]] > Sent: Saturday, June 08, 2002 1:15 AM > To: JDJList > Subject: [jdjlist] SQL Server and stored procedure > > Hi all > > I am using the latest SQL Server 2000 JDBC driver from Microsoft, > obviously connecting to SQL Server, and have trouble getting the > return value from stored procedure. > > I have no problem to read OUT parameters. For example if I have a > procedure declared as: > > CREATE PROC TEST_PROC > @NEW_ID VARCHAR2(10) > > SELECT @NEW_ID = '123456' > RETURN 0 > > I can call and read the value of NEW_ID. > > But if I have a procedure > > CREATE PROC TEST_PROC2 > > DECLARE @RESULT > SELECT @RESULT = some_value > > RETURN @RESULT > > I can not read the returned value. The included documentation > recommends the syntax: > > {[?=]call procedure-name[([parameter][,[parameter]]...)]} > but the driver does not like it. > Does anyone know the proper syntax to get the returned value? > > Thanks > > Witold > > To change your membership options, refer to: > http://www.sys-con.com/java/list.cfm > > > > To change your membership options, refer to: > http://www.sys-con.com/java/list.cfm To change your membership options, refer to: http://www.sys-con.com/java/list.cfm To change your membership options, refer to: http://www.sys-con.com/java/list.cfm
