springboot:calling_stored_procedure_with_output_parameters

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")};
    }
  • springboot/calling_stored_procedure_with_output_parameters.txt
  • Last modified: 2020/12/29 14:51
  • by chongtin