java调用sql server存储过程

为了便于测试，先创建一个简单的表：

 

CREATE TABLE test
(
	id INT,
	name VARCHAR(10)
)
INSERT INTO test VALUES(1,'safsdf');
INSERT INTO test VALUES(2,'aaaaa');
INSERT INTO test VALUES(3,'bbbbb');

 

一.存储过程返回单个结果集

存储过程1如下所示:

 

IF OBJECT_ID ('dbo.pTestProc1') IS NOT NULL
	DROP PROCEDURE dbo.pTestProc1
GO
CREATE PROCEDURE pTestProc1
(
	@id INT,
	@name VARCHAR(20) OUTPUT
)
AS
BEGIN
	SELECT @name = name FROM test WHERE id = @id
END 

 存储过程2如下所示:

 

IF OBJECT_ID ('dbo.pTestProc2') IS NOT NULL
	DROP PROCEDURE dbo.pTestProc2
GO
CREATE PROCEDURE pTestProc2
AS
BEGIN
DECLARE @sql VARCHAR(100)
   SET @sql = 'SELECT id, name FROM test '
   EXEC(@sql)
END 

 

 调用存储过程１,2的代码如下({call dbo.pTestProc1或2})：

 

Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("{call dbo.pTestProc1}");
		while (rs.next()) {
			System.out.println(rs.getString("id") + ":" + rs.getString("name"));
		}

输出结果如下：

1:safsdf

2:aaaaa

3:bbbbb

 

 

二.使用带有输入参数的存储过程

存储过程3如下所示:

 

IF OBJECT_ID ('dbo.pTestProc3') IS NOT NULL
	DROP PROCEDURE dbo.pTestProc3
GO
CREATE PROCEDURE pTestProc3
(
	@id INT
)
AS
BEGIN
	SELECT id, name  FROM test WHERE id = @id
END 

  调用代码如下：

 

Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("{call pTestProc3(1)}");
		while (rs.next()) {
			System.out.println(rs.getString("id") + ":" + rs.getString("name"));
		}
		或
	PreparedStatement pstmt = conn.prepareStatement("{call pTestProc3(?)}"); 
		 pstmt.setInt(1, 1); 
		 ResultSet rs = pstmt.executeQuery(); 
		 while (rs.next()) {
				System.out.println(rs.getString("id") + ":" + rs.getString("name"));
			}
	或
	CallableStatement cstmt = conn.prepareCall("{call dbo.pTestProc3(?)}"); 
		cstmt.setInt(1, 1); 
	ResultSet rs = cstmt.executeQuery(); 
		while (rs.next())  
			System.out.println(rs.getString("id") + ":" + rs.getString("name"));

 输出结果都是:

1:safsdf

 

 

三.使用带有输出参数的存储过程

存储过程4如下所示:

 

IF OBJECT_ID ('dbo.pTestProc4') IS NOT NULL
	DROP PROCEDURE dbo.pTestProc4
GO
CREATE PROCEDURE pTestProc4
(
	@id INT,
	@name VARCHAR(20) OUTPUT
)
AS
BEGIN
	SELECT @name = name FROM test WHERE id = @id
END 

 调用代码如下:

 

CallableStatement cstmt = conn.prepareCall("{ call dbo.pTestProc4(?,?)}");
		 //设置IN参数
		 cstmt.setInt(1,1);
		 //注册OUT参数
		 cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
		 //执行存储过程
		 cstmt.execute();
		//获取参数值
		 System.out.println( cstmt.getString(2) );

输出结果：safsdf 

 

参考文章：http://www.qqread.com/java/2008/04/w407397.html