我在程序中要调用一个带output参数的存储过程,根据返回的output参数值来判断存储过程里的sql是否都正确的执行。那么在程序中该怎么写调用这种存储过程,还有就是怎样得到返回的output参数值。
CREATE PROCEDURE InsertNewsCont
@NewsTitle nvarchar(50),
@NewsContent ntext,
@NewsFrom nvarchar(50),
@ReaSon nvarchar(50) output
AS
select * from NewsTB where NewsTitle= @NewsTitle
if @@rowcount<1
begin
insert into NewsTB(NewsTitle,NewsContent,NewsFrom) Values(@NewsTitle,@NewsContent,@NewsFrom)
end
else
begin
set @ReaSon='对不起,有重复标题!'
end
GO
CREATE PROCEDURE InsertNewsCont
@NewsTitle nvarchar(50),
@NewsContent ntext,
@NewsFrom nvarchar(50),
@ReaSon nvarchar(50) output
AS
select * from NewsTB where NewsTitle= @NewsTitle
if @@rowcount<1
begin
insert into NewsTB(NewsTitle,NewsContent,NewsFrom) Values(@NewsTitle,@NewsContent,@NewsFrom)
end
else
begin
set @ReaSon='对不起,有重复标题!'
end
GO
1、执行存储过程并返回dataset
public override DataSet cusProcData(string ProcName,OleDbParameter[] param,string tablename)
{
DataSet ds = new DataSet();
OleDbCommand oleDbCom = new OleDbCommand();
OleDbDataAdapter ad = new OleDbDataAdapter();
oleDbCom.Connection = conn;
if(inTransaction)
oleDbCom.Transaction=trans;
oleDbCom.CommandType=System.Data.CommandType.StoredProcedure;
oleDbCom.CommandText=ProcName;
if (param.Length>0)
{
for(int i = 0;i <= param.Length - 1;i++)
{
if (param[i]!=null) oleDbCom.Parameters.Add(param[i]);
}
}
ad.SelectCommand = oleDbCom;
ad.Fill(ds,tablename);
return ds;
}2、调用方法(其中RoleID就是output参数)
Dim param() As OleDbParameter = {New OleDbParameter("@CategoryID", OleDbType.Integer, 1), _
New OleDbParameter("@RoleName", OleDbType.VarChar, 50), _
New OleDbParameter("@Description", OleDbType.VarChar, 50), _
New OleDbParameter("@RoleID", OleDbType.Integer, 10) _
} param(0).Value = "1"
param(1).Value = "2"
param(2).Value = "sss"
param(3).Direction = ParameterDirection.Output Dim ds As DataSet
ds = _cus.cusProcData("sp_AccountRole_Create", param, "sp_AccountRole_Create")
Dim s As String
s = CStr(param(3).Value) '在这里,s就是你要返回的output的值