Stefan
Thanks for the reply.
I have found my syntax error so can read the return value.
If I use your suggestion:
select '<return_value>'
how would I read it in java - would it be first parameter, something like:
csmt = con.prepareCall( "{? = call sp_BHT ?, ?, ?, ?}" );
and use like other out parameter?
csmt.registerOutParameter( 1, Types.VARCHAR );
...
String ret_val = cstmt.getString( 1 );
My code doesn't seem to read the value
Thanks
Witold
On 11 Jun 2002 at 12:05, Stefan Carlsson wrote:
>
> 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
==================================
Witold Iwaniec
Sr Software Developer
NovaLIS Technologies
[EMAIL PROTECTED]
http://www.novalistech.com
To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm
