Calling Stored Procedure with Output parameters
Assume we have a stored procedure like this in our database:
USE [YOUR_DATABASE_NAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[TestParamOutput] @test_input1 INT, @test_input2 INT, @test_input3 INT, @test_input4 INT, @test_output1 INT OUTPUT, @test_output2 INT OUTPUT, @test_output3 INT OUTPUT, @test_output4 INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @test_output1 = @test_input1+@test_input2; SELECT @test_output2 = @test_input3+@test_input4; SELECT @test_output3 = @test_input1*@test_input2; SELECT @test_output4 = @test_input3*@test_input4; END
We can do the following in our service class. Note that the name procedure name, and the parameter names should match.
@PersistenceContext private EntityManager em; public int[] getSPOutput(){ StoredProcedureQuery proc = em.createStoredProcedureQuery("TestParamOutput"); proc.registerStoredProcedureParameter("test_input4", Integer.class, ParameterMode.IN); proc.registerStoredProcedureParameter("test_input1", Integer.class, ParameterMode.IN); proc.registerStoredProcedureParameter("test_input2", Integer.class, ParameterMode.IN); proc.registerStoredProcedureParameter("test_input3", Integer.class, ParameterMode.IN); proc.registerStoredProcedureParameter("test_output4", Integer.class, ParameterMode.OUT); proc.registerStoredProcedureParameter("test_output3", Integer.class, ParameterMode.OUT); proc.registerStoredProcedureParameter("test_output2", Integer.class, ParameterMode.OUT); proc.registerStoredProcedureParameter("test_output1", Integer.class, ParameterMode.OUT); proc.setParameter("test_input1", 1); proc.setParameter("test_input2", 2); proc.setParameter("test_input3", 3); proc.setParameter("test_input4", 4); proc.execute(); return new int[]{(int) proc.getOutputParameterValue("test_output1"), (int) proc.getOutputParameterValue("test_output2"), (int) proc.getOutputParameterValue("test_output3"), (int) proc.getOutputParameterValue("test_output4")}; }