--取得可用容量
IF OBJECT_ID (N'getAvailableVolume', N'P') IS NOT NULL
DROP PROCEDURE getAvailableVolume;
GO
CREATE PROCEDURE getAvailableVolume
@kuwei varchar(50) = NULL,
@volume Decimal OUTPUT,
@volume2 Decimal OUTPUT
AS
IF @kuwei IS NULL
BEGIN
PRINT '库位不能为空！'
RETURN
END
--取得库存辅数量
Begin
Set @volume = (
SELECT SUM(SecondQuantity) as r
FROM daiKuCun
WHERE KuWei = @kuwei
)
End
--判断库存辅数量是否为空
IF @volume IS NULL
BEGIN
Set @volume =0
END
--将没有过帐的辅数量纳入统计范围
Begin
Set @volume2 = (
SELECT SUM(SecondQuantity) AS r
FROM daiIn
WHERE (State = 0) AND (KuWei = @kuwei)
)
End
--判断库存辅数量是否为空
IF @volume2 IS NULL
BEGIN
Set @volume2 =0
END
--取得可用容量
Begin
Set @volume = (SELECT Volume FROM daiKuWei WHERE Code = @kuwei)-@volume-@volume2
End
--输入可用容量
Select @volume as r
RETURN
GO
调用存储过程
EXEC getAvailableVolume 'A1',0,0
C#调用存储过程
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConnString;
try
{
conn.Open();
SqlCommand comd = new SqlCommand();
comd.Connection = conn;
comd.CommandText = sql;
comd.CommandType = CommandType.StoredProcedure;
//
SqlParameter param = new SqlParameter("@kuwei", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = kuwei;
comd.Parameters.Add(param);
//
param = new SqlParameter("@volume", SqlDbType.Decimal);
param.Direction = ParameterDirection.Output;
param.Value = 0;
comd.Parameters.Add(param);
//
param = new SqlParameter("@volume2", SqlDbType.Decimal);
param.Direction = ParameterDirection.Output;
param.Value = 0;
comd.Parameters.Add(param);
SqlDataReader reader = comd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Response.Write("<?xml version=""1.0"" encoding=""utf-8"" ?>"n");
Response.Write("<tname>"n");
if (reader[return_field] != System.DBNull.Value)
{
Response.Write("<id>");
Response.Write(reader[return_field].ToString());
Response.Write("</id>"n");
}
Response.Write("</tname>");
}
}
reader.Close();
reader.Dispose();
comd.Dispose();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
}