Executing User-defined SQL Server function in Java

Question

I have this SQL function:

CREATE FUNCTION Average (@year int)
RETURNS decimal(4,2)
AS
BEGIN
    DECLARE @result as decimal(4,2);
    SET @result = (SELECT AVG(CAST(MilkQuantity AS decimal(4,2))) FROM MilkProduction WHERE YEAR(Date) = @year);
    RETURN @result;
END
GO

How do I execute this function in java and what data type do I even bind it to?

This is what I came up with and it works but it seems like I'm coloring outside the lines here:

PreparedStatement ps = con.prepareStatement("SELECT dbo.Average(" + year + ")");
ResultSet rs = ps.executeQuery();
rs.next();
System.out.println("Average: " + rs.getFloat(""));

This works depending on the database engine. For example, this won't work on oracle. — Luiggi Mendoza
– Luiggi Mendoza, Commented Sep 20, 2013 at 23:34

Jitendra · Accepted Answer · 2015-12-11 10:13:56Z

1

Use CallableStatement as below: CallableStatement stmt = null; String sql = "begin ? := Average(?); end;"; ResultSet rs = null; try { stmt = conn.prepareCall(sql); stmt.registerOutParameter(1, INT); stmt.setString(2, "VALUE"); stmt.execute(); } Catch(){ ....}

answered Dec 11, 2015 at 10:13

Jitendra

705 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing User-defined SQL Server function in Java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related