CREATE PROCEDURE GetImmediateManager @employeeID INT, @managerID INT OUTPUT AS BEGIN SELECT @managerID = ManagerID FROM HumanResources.Employee WHERE EmployeeID = @employeeID END
create procedure getUserCount @usercount int out as select @usercount=count(*) from tab_usergo
作为实例,在 SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程: 复制代码 CREATE PROCEDURE GetImmediateManager @employeeID INT, @managerID INT OUTPUT AS BEGIN SELECT @managerID = ManagerID FROM HumanResources.Employee WHERE EmployeeID = @employeeID END 根据指定的整数 IN 参数 (employeeID),该存储过程也返回单个整数 OUT 参数 (managerID)。根据 HumanResources.Employee 表中包含的 EmployeeID,OUT 参数中返回的值为 ManagerID。在下面的实例中,将向此函数传递 AdventureWorks 示例数据库的打开连接,然后使用 execute 方法调用 GetImmediateManager 存储过程: 复制代码 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(); } }
CREATE PROCEDURE GetImmediateManager @employeeID INT, @managerID INT OUTPUT AS BEGIN SELECT @managerID = ManagerID FROM HumanResources.Employee WHERE EmployeeID = @employeeID END
@iReturn int output
ASselect @iReturn =count(1) from t1
return 111
GO
private void button1_Click_1(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=db;Persist Security Info=True;User ID=sa;Password=12345;");
SqlCommand cmd = new SqlCommand("GetCount", conn); cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parm = {new SqlParameter("@iReturn",SqlDbType.Int),
new SqlParameter("@XX",SqlDbType.NVarChar)
}; //输出参数
parm[0] = cmd.Parameters.Add("@iReturn", SqlDbType.Int);
parm[0].Direction = ParameterDirection.Output; //返回值
parm[1] = cmd.Parameters.Add("@XX", SqlDbType.Int);
parm[1].Direction = ParameterDirection.ReturnValue; conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); MessageBox.Show(parm[0].Value.ToString() + " " + parm[1].Value.ToString()); reader.Close();
conn.Close();
}
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
create procedure getUserCount
@usercount int out
as
select @usercount=count(*) from tab_usergo
作为实例,在 SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程: 复制代码
CREATE PROCEDURE GetImmediateManager
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END 根据指定的整数 IN 参数 (employeeID),该存储过程也返回单个整数 OUT 参数 (managerID)。根据 HumanResources.Employee 表中包含的 EmployeeID,OUT 参数中返回的值为 ManagerID。在下面的实例中,将向此函数传递 AdventureWorks 示例数据库的打开连接,然后使用 execute 方法调用 GetImmediateManager 存储过程: 复制代码
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();
}
}
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END