Using a Stored Procedure with Output Parameters

A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application. The Microsoft SQL Server 2005 JDBC Driver provides the SQLServerCallableStatement class, which you can use to call this kind of stored procedure and process the data that it returns.

When you call this kind of stored procedure by using the JDBC driver, you must use the call SQL escape sequence together with the prepareCallmethod of the SQLServerConnection class. The syntax for the call escape sequence with OUT parameters is the following:

{call procedure-name[([parameter][,[parameter]]...)]}

Note:
For more information about the SQL escape sequences, see Using SQL Escape Sequences.

When you construct the call escape sequence, specify the OUT parameters by using the ? (question mark) character. This character acts as a placeholder for the parameter values that will be returned from the stored procedure. To specify a value for an OUT parameter, you must specify the data type of each parameter by using the registerOutParameter method of the SQLServerCallableStatement class before you run the stored procedure.

The value that you specify for the OUT parameter in the registerOutParameter method must be one of the JDBC data types contained in java.sql.Types, which in turn maps to one of the native SQL Server data types. For more information about the JDBC and SQL Server data types, seeUnderstanding the JDBC Driver Data Types.

When you pass a value to the registerOutParameter method for an OUT parameter, you must specify not only the data type to be used for the parameter, but also the parameter's ordinal placement or the parameter's name in the stored procedure. For example, if your stored procedure contains a single OUT parameter, its ordinal value will be 1; if the stored procedure contains two parameters, the first ordinal value will be 1, and the second ordinal value will be 2.

Note:
The JDBC driver does not support the use of CURSOR, SQLVARIANT, TABLE, and TIMESTAMP SQL Server data types as OUT parameters.

As an example, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database:

CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
   SELECT @managerID = ManagerID 
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID
END

This stored procedure returns a single OUT parameter (managerID), which is an integer, based on the specified IN parameter (employeeID), which is also an integer. The value that is returned in the OUT parameter is the ManagerID based on the EmployeeID that is contained in the HumanResources.Employee table.

In the following example, an open connection to the AdventureWorks sample database is passed in to the function, and the execute method is used to call the GetImmediateManager stored procedure:

public static void executeStoredProcedure(Connection con) {
   try {
      CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
      cstmt.setInt(1, 5);
      cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
      cstmt.execute();
      System.out.println("MANAGER ID: " + cstmt.getInt(2));
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}

This example uses the ordinal positions to identify the parameters. Alternatively, you can identify a parameter by using its name instead of its ordinal position. The following code example modifies the previous example to demonstrate how to use named parameters in a Java application. Note that parameter names correspond to the parameter names in the stored procedure's definition:

public static void executeStoredProcedure(Connection con) {
   try {
      CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
      cstmt.setInt("employeeID", 5);
      cstmt.registerOutParameter("managerID", java.sql.Types.INTEGER);
      cstmt.execute();
      System.out.println("MANAGER ID: " + cstmt.getInt("managerID"));
      cstmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }

}

Note:
These examples use the execute method of the SQLServerCallableStatement class to run the stored procedure. This is used because the stored procedure did not also return a result set. If it did, the executeQuery method would be used.

Stored procedures can return update counts and multiple result sets. The Microsoft SQL Server 2005 JDBC Driver follows the JDBC 3.0 specification, which states that multiple result sets and update counts should be retrieved before the OUT parameters are retrieved. That is, the application should retrieve all of the ResultSet objects and update counts before retrieving the OUT parameters by using the CallableStatement.getter methods. Otherwise, the ResultSet objects and update counts that have not already been retrieved will be lost when the OUT parameters are retrieved. For more information about update counts and multiple result sets, see Using a Stored Procedure with an Update Count and Using Multiple Result Sets.

Read More

How to get return value in function sql by Java

Function in sql server

create function checkFunc(
 @MaSP varchar(10)
)
returns int
AS
BEGIN
 declare @kq int
 IF EXISTS(SELECT * FROM SANPHAM WHERE MaSP=@MaSP) set @kq=2
 ELSE 
  BEGIN
   set @kq = 0
  END
 return @kq
END
//execute
select dbo.checkFunc('sp02') 

How to get data by resultset in java

public int checkSanPham() {
  sql = "select dbo.checkFunc(?)";
  connection = connectDB.getConnect();
  int kq = 0;
  try {
   ps = connection.prepareStatement(sql);
   ps.setString(1, "sp02");
   rs = ps.executeQuery();
   if(rs.next()){
    System.out.println(rs.getInt(1));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    rs.close();
    ps.close();
    connection.close();
   } catch (Exception e2) {
   }
  }
  
  return kq;
 }

SQL.

How to use CallableStatement 

store-proceduce in sql

 
create proc sp_insert 
 @MaSP varchar(10),
 @TenSP varchar(50),
 @MaLoaiSP varchar(10),
 @GiaBan int,
 @SoLuong int
AS
BEGIN 
 --kiem tra Ma loai san pham co ton tai trong bang LOAISP
 IF EXISTS(SELECT TOP 1 MaLoaiSP FROM LOAISP WHERE MaLoaiSP = @MaLoaiSP)
  BEGIN
   -- Kiem tra neu k ton tai ma san pham thi cho insert
   IF NOT EXISTS(SELECT TOP 1 MaSP FROM SANPHAM WHERE MaSP = @MaSP)
    BEGIN
     INSERT INTO SANPHAM (MaSP,TenSP,MaLoaiSP,GiaBan,SoLuong) VALUES(@MaSP, @TenSP, @MaLoaiSP, @GiaBan, @SoLuong)
    END
   ELSE
    BEGIN
     print ('Trung khoa chinh')
    END
  END
 ELSE
  BEGIN
   print ('MaLoaiSP khong ton tai')
  END
END

--execute
exec sp_insert 'sp09','kaka','type001','10','10'

Insert method in Java

public void themSanPham(String maSP, String tenSP, String maLoaiSP,
   String giaBan, String soLuong) {
  sql = "{call sp_insert(?,?,?,?,?)}";
  connection = connectDB.getConnect();
  CallableStatement cs = null;
  try {
   cs = connection.prepareCall(sql);
   cs.setString(1, maSP);
   cs.setString(2, tenSP);
   cs.setString(3, maLoaiSP);
   cs.setInt(4, Integer.valueOf(giaBan));
   cs.setInt(5, Integer.valueOf(soLuong));
   int i = cs.executeUpdate();
   System.out.println(i);
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    cs.close();
    connection.close();
   } catch (Exception e2) {
   }
  }
  
 }

Khi update, or insert data by store-procedure thì khi update, insert thành công thì nó sẽ trả về giá trị 1, fail thì trả về -1. ta lấy giá trị này để xác định là thành công hay chưa.
                        int i = cs.executeUpdate();

System.out.println(i);

Read More

How to get return value in function sql by Java

Function in sql server

create function checkFunc(
 @MaSP varchar(10)
)
returns int
AS
BEGIN
 declare @kq int
 IF EXISTS(SELECT * FROM SANPHAM WHERE MaSP=@MaSP) set @kq=2
 ELSE 
  BEGIN
   set @kq = 0
  END
 return @kq
END
//execute
select dbo.checkFunc('sp02') 

How to get data by resultset in java

public int checkSanPham() {
  sql = "select dbo.checkFunc(?)";
  connection = connectDB.getConnect();
  int kq = 0;
  try {
   ps = connection.prepareStatement(sql);
   ps.setString(1, "sp02");
   rs = ps.executeQuery();
   if(rs.next()){
    System.out.println(rs.getInt(1));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    rs.close();
    ps.close();
    connection.close();
   } catch (Exception e2) {
   }
  }
  
  return kq;
 }

SQL.

How to use CallableStatement 

store-proceduce in sql

 
create proc sp_insert 
 @MaSP varchar(10),
 @TenSP varchar(50),
 @MaLoaiSP varchar(10),
 @GiaBan int,
 @SoLuong int
AS
BEGIN 
 --kiem tra Ma loai san pham co ton tai trong bang LOAISP
 IF EXISTS(SELECT TOP 1 MaLoaiSP FROM LOAISP WHERE MaLoaiSP = @MaLoaiSP)
  BEGIN
   -- Kiem tra neu k ton tai ma san pham thi cho insert
   IF NOT EXISTS(SELECT TOP 1 MaSP FROM SANPHAM WHERE MaSP = @MaSP)
    BEGIN
     INSERT INTO SANPHAM (MaSP,TenSP,MaLoaiSP,GiaBan,SoLuong) VALUES(@MaSP, @TenSP, @MaLoaiSP, @GiaBan, @SoLuong)
    END
   ELSE
    BEGIN
     print ('Trung khoa chinh')
    END
  END
 ELSE
  BEGIN
   print ('MaLoaiSP khong ton tai')
  END
END

--execute
exec sp_insert 'sp09','kaka','type001','10','10'

Insert method in Java

public void themSanPham(String maSP, String tenSP, String maLoaiSP,
   String giaBan, String soLuong) {
  sql = "{call sp_insert(?,?,?,?,?)}";
  connection = connectDB.getConnect();
  CallableStatement cs = null;
  try {
   cs = connection.prepareCall(sql);
   cs.setString(1, maSP);
   cs.setString(2, tenSP);
   cs.setString(3, maLoaiSP);
   cs.setInt(4, Integer.valueOf(giaBan));
   cs.setInt(5, Integer.valueOf(soLuong));
   int i = cs.executeUpdate();
   System.out.println(i);
   if(i > 0){
    System.out.println("update thanh cong");
   } else {
    System.out.println("fail update!");
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    cs.close();
    connection.close();
   } catch (Exception e2) {
   }
  }
  
 }

Khi update, or insert data by store-procedure thì khi update, insert thành công thì nó sẽ trả về giá trị 1, fail thì trả về -1. ta lấy giá trị này để xác định là thành công hay chưa.
                        int i = cs.executeUpdate();

System.out.println(i);

Read More