CREATE proc TotalMoney
@SupplierName varchar(15),@ShouldPay money output,@SolidPay money output,@NoPay money output
as
select @ShouldPay=SUM(应收金额), @SolidPay=SUM(已收金额),@NoPay=SUM(应收金额)-SUM(已收金额) from 入库信息 where 供应商名称 like @SupplierName
set @ShouldPay=@ShouldPay
set @SolidPay=@SolidPay
set @NoPay=@NoPay
RETURN @@ROWCOUNT
GO我想从存储过程返回 应收金额,已收金额,未付金额 不知这样的存储过程是否正确!!!
再就是在C#或VB.NET中调用上面的存储过程的返回值是怎么实现的,希望大家帮帮我,我卡在这里已经两三天了,无法完成这个就无法完成任务呀!这个是从Msdn中搜索到的:改了一些东西,能返回值,但我想要的不是这些:存储过程:
CREATE PROCEDURE InsertCategory
@CategoryName nchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = @@Identity
RETURN @@ROWCOUNTC#中的调用代码:
SqlConnection nwindConn = new SqlConnection("Data Source=goodview;Integrated Security=SSPI;" +
"Initial Catalog=northwind"); SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn); catDA.InsertCommand = new SqlCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure; SqlParameter myParm = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
myParm.Direction = ParameterDirection.ReturnValue; catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName"); myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output; DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories"); DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow); catDA.Update(catDS, "Categories"); Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value;
textBox1.Text=rowCount.ToString();
int inText=(int)catDA.InsertCommand.Parameters["@Identity"].Value;
textBox2.Text=inText.ToString();
string strName=(string)catDA.InsertCommand.Parameters["@CategoryName"].Value;
textBox3.Text=strName.ToString();
@SupplierName varchar(15),@ShouldPay money output,@SolidPay money output,@NoPay money output
as
select @ShouldPay=SUM(应收金额), @SolidPay=SUM(已收金额),@NoPay=SUM(应收金额)-SUM(已收金额) from 入库信息 where 供应商名称 like @SupplierName
set @ShouldPay=@ShouldPay
set @SolidPay=@SolidPay
set @NoPay=@NoPay
RETURN @@ROWCOUNT
GO我想从存储过程返回 应收金额,已收金额,未付金额 不知这样的存储过程是否正确!!!
再就是在C#或VB.NET中调用上面的存储过程的返回值是怎么实现的,希望大家帮帮我,我卡在这里已经两三天了,无法完成这个就无法完成任务呀!这个是从Msdn中搜索到的:改了一些东西,能返回值,但我想要的不是这些:存储过程:
CREATE PROCEDURE InsertCategory
@CategoryName nchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = @@Identity
RETURN @@ROWCOUNTC#中的调用代码:
SqlConnection nwindConn = new SqlConnection("Data Source=goodview;Integrated Security=SSPI;" +
"Initial Catalog=northwind"); SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn); catDA.InsertCommand = new SqlCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure; SqlParameter myParm = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
myParm.Direction = ParameterDirection.ReturnValue; catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName"); myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output; DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories"); DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow); catDA.Update(catDS, "Categories"); Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value;
textBox1.Text=rowCount.ToString();
int inText=(int)catDA.InsertCommand.Parameters["@Identity"].Value;
textBox2.Text=inText.ToString();
string strName=(string)catDA.InsertCommand.Parameters["@CategoryName"].Value;
textBox3.Text=strName.ToString();
执行后,
xxxx=xxxcommand.Parameters["aaa"].value 大致如此
set @ShouldPay=@ShouldPay
set @SolidPay=@SolidPay
set @NoPay=@NoPay
不要后面这三个set
@SupplierName varchar(15),@ShouldPay money output,@SolidPay money output,@NoPay money output
as
select @ShouldPay=SUM(应收金额), @SolidPay=SUM(已收金额),@NoPay=SUM(应收金额)-SUM(已收金额) from 入库信息 where 供应商名称 like @SupplierName
GO
这样就差不多了,带参数的like子句你注意一下,我忘了怎么写合适了。
用的时候也是先创建ParameterDirection.Output形式的参数,
ExecuteNoQuery后再取参数的Value