By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,417 Members | 1,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,417 IT Pros & Developers. It's quick & easy.

retrieve an output parameter in stored procedure from java application

P: n/a
in my java application I've made a call to this stored procedure

CREATE procedure pruebaICM
@pANI varchar(20),
@pTABLA varchar(20),
@pInsert varchar(500),
@pUpdate varchar(1000),
@pFLAG varchar(1),
@pResultado int OUTPUT

as
begin

DECLARE @ani varchar(20)
declare @cliente int
DECLARE @sentencia nvarchar(1000)
DECLARE @tabla nvarchar(20)
DECLARE @sentencia_where nvarchar(50)
DECLARE @sql nvarchar(1050)
SET NOCOUNT ON
set @tabla = @pTABLA
set @ani = @pANI
SELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' where
ani = ' + @ani
exec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUT
SELECT @Cliente
if (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')
begin
if (@cliente = 0)
begin
set @sentencia = N'insert into ' +
@pTABLA + N' values (' + @pInsert + N')'
EXEC sp_executesql @sentencia
set @pResultado = 1
SELECT @pResultado
return @pResultado
end

if (@cliente = 1)
begin
set @sentencia = N'update ' + @pTABLA +
N' set ' + @pUpdate
set @sentencia_where = N' where ANI =
' + @pANI
set @sql = @sentencia +
@sentencia_where
EXEC sp_executesql @sql
set @pResultado = 2
SELECT @pResultado
return @pResultado
end

end
else if (@pFLAG = 'B') or (@pFLAG = 'Borrar')
begin

if (@cliente = 0)
begin
set @pResultado = 0
SELECT @pResultado
return @pResultado
end
else if (@cliente = 1)
begin
set @sentencia = N'delete from '+
@pTABLA + N' where ANI = ' + @pANI
EXEC sp_executesql @sentencia
set @pResultado = 3
SELECT @pResultado
return @pResultado
end
end
EXEC sp_cursorclose
end

My problem is that the ouutput param @pResultado haven't got any value
and don't return anything to the java application. How can I fix this
problem?

Thanka very much for helping me!!!!
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
be**********@yahoo.es (Begoņa) wrote in message news:<c0**************************@posting.google. com>...
in my java application I've made a call to this stored procedure

CREATE procedure pruebaICM
@pANI varchar(20),
@pTABLA varchar(20),
@pInsert varchar(500),
@pUpdate varchar(1000),
@pFLAG varchar(1),
@pResultado int OUTPUT

as
begin

DECLARE @ani varchar(20)
declare @cliente int
DECLARE @sentencia nvarchar(1000)
DECLARE @tabla nvarchar(20)
DECLARE @sentencia_where nvarchar(50)
DECLARE @sql nvarchar(1050)
SET NOCOUNT ON
set @tabla = @pTABLA
set @ani = @pANI
SELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' where
ani = ' + @ani
exec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUT
SELECT @Cliente
if (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')
begin
if (@cliente = 0)
begin
set @sentencia = N'insert into ' +
@pTABLA + N' values (' + @pInsert + N')'
EXEC sp_executesql @sentencia
set @pResultado = 1
SELECT @pResultado
return @pResultado
end

if (@cliente = 1)
begin
set @sentencia = N'update ' + @pTABLA +
N' set ' + @pUpdate
set @sentencia_where = N' where ANI =
' + @pANI
set @sql = @sentencia +
@sentencia_where
EXEC sp_executesql @sql
set @pResultado = 2
SELECT @pResultado
return @pResultado
end

end
else if (@pFLAG = 'B') or (@pFLAG = 'Borrar')
begin

if (@cliente = 0)
begin
set @pResultado = 0
SELECT @pResultado
return @pResultado
end
else if (@cliente = 1)
begin
set @sentencia = N'delete from '+
@pTABLA + N' where ANI = ' + @pANI
EXEC sp_executesql @sentencia
set @pResultado = 3
SELECT @pResultado
return @pResultado
end
end
EXEC sp_cursorclose
end

My problem is that the ouutput param @pResultado haven't got any value
and don't return anything to the java application. How can I fix this
problem?

Thanka very much for helping me!!!!


You can try a couple of things to identify the problem. First, you can
use SELECT @sentencia and SELECT @sql in the procedure to make sure
that you are executing the code that you think you're executing.
Second, use the stored procedure debugger to check that the procedure
is following the logic that you want it to - the debugger will also
display the value of @pResultado, so you should be able to see why it
isn't being assigned a value.

Simon
Jul 20 '05 #2

P: n/a
If I execute this stored procedure inside query analyzer, it works
well; i see the value of @pResultado due to command "select
@pResultado"
and it's OK but the problem remainds when i try to retrieve this param
from my java application. I change de type of this param (@pResultado)
to varchar(1) and then i have the command System.out.print in java
application but the value es "" 8no value return).
Any idea???
Thanks very much for your help
sq*@hayes.ch (Simon Hayes) wrote in message news:<60**************************@posting.google. com>...
be**********@yahoo.es (Begoņa) wrote in message news:<c0**************************@posting.google. com>...
in my java application I've made a call to this stored procedure

CREATE procedure pruebaICM
@pANI varchar(20),
@pTABLA varchar(20),
@pInsert varchar(500),
@pUpdate varchar(1000),
@pFLAG varchar(1),
@pResultado int OUTPUT

as
begin

DECLARE @ani varchar(20)
declare @cliente int
DECLARE @sentencia nvarchar(1000)
DECLARE @tabla nvarchar(20)
DECLARE @sentencia_where nvarchar(50)
DECLARE @sql nvarchar(1050)
SET NOCOUNT ON
set @tabla = @pTABLA
set @ani = @pANI
SELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' where
ani = ' + @ani
exec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUT
SELECT @Cliente
if (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')
begin
if (@cliente = 0)
begin
set @sentencia = N'insert into ' +
@pTABLA + N' values (' + @pInsert + N')'
EXEC sp_executesql @sentencia
set @pResultado = 1
SELECT @pResultado
return @pResultado
end

if (@cliente = 1)
begin
set @sentencia = N'update ' + @pTABLA +
N' set ' + @pUpdate
set @sentencia_where = N' where ANI =
' + @pANI
set @sql = @sentencia +
@sentencia_where
EXEC sp_executesql @sql
set @pResultado = 2
SELECT @pResultado
return @pResultado
end

end
else if (@pFLAG = 'B') or (@pFLAG = 'Borrar')
begin

if (@cliente = 0)
begin
set @pResultado = 0
SELECT @pResultado
return @pResultado
end
else if (@cliente = 1)
begin
set @sentencia = N'delete from '+
@pTABLA + N' where ANI = ' + @pANI
EXEC sp_executesql @sentencia
set @pResultado = 3
SELECT @pResultado
return @pResultado
end
end
EXEC sp_cursorclose
end

My problem is that the ouutput param @pResultado haven't got any value
and don't return anything to the java application. How can I fix this
problem?

Thanka very much for helping me!!!!


You can try a couple of things to identify the problem. First, you can
use SELECT @sentencia and SELECT @sql in the procedure to make sure
that you are executing the code that you think you're executing.
Second, use the stored procedure debugger to check that the procedure
is following the logic that you want it to - the debugger will also
display the value of @pResultado, so you should be able to see why it
isn't being assigned a value.

Simon

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.