50分求存储过程返回值

[chuxue1342]

存储过程如下:
CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int
set @comcount=(select count(*) from pmc where comtype=@comtype
GO
------------------
我要用count.text得到@comcount的值,怎么做??

[Tomming2002]

CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int
set @comcount=(select count(*) from pmc where comtype=@comtype
..
select @comcount '输出
GO
然后执行存储过程,输出到结果集

[chuxue1342]

这个不是跟我的一样吗?我就是不知道后台怎么得到值啊

[babyrockxray]

存储过程没写对
CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int
set @comcount=(select count(*) from pmc where comtype=@comtype)
return @comcount
GO

SqlConnection con=new SqlConnection(connectionstring);
con.Open();
SqlCommand cmd=new SqlCommand(con);
cmd.CommandText="pmc_count";
cmd.CommandType=System.Data.CommandType.StoredProcedure;
SqlParameter pa=new SqlParameter("@comtype",System.Data.SqlDbType.VarChar,50);
pa.Value = "testvalue";
cmd.Parameters.Add(pa);

int returnValue = (int)cmd.ExecuteScalar();
count.Text = returnValue.ToString();

[北京的雾霾天]

把@comcount 定义成output类型的参数，由存储过程返回这个值：

CREATE PROCEDURE pmc_count
(
@comtype varchar(50)，
@comcount int output
)
as
set @comcount=(select count(*) from pmc where comtype=@comtype
GO

[北京的雾霾天]

或者用Return返回，比如：


CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int
set @comcount=(select count(*) from pmc where comtype=@comtype
return @comcount
GO


当然在程序里，SqlParameter.Direction 属性要设置为相应的
Output 参数是输出参数。
或
ReturnValue 参数表示诸如存储过程、内置函数或用户定义函数之类的操作的返回值。

[yourname386]

CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int

select @comcount=(select count(*) from pmc where comtype=@comtype


GO

[北京的雾霾天]

建议使用参数返回或用存储过程的Return返回单个值，如果用Select的方式返回，那么请使用SqlCommand.ExecuteScalar 方法返回单个值.

[Tomming2002]

如果这个存储过程只需要返回 @comcount这个值的话
紫色阴影 的答案标准
可是有必要为了一个值写个存储过程吗?

return @comcount后的语句不会执行的.
如果返回多个结果集可以使用
select @comcount
其他select

然后存储过程的输出为结果集,引用第一个表就是 @comcount,其他select语句输出的就是表1....

[james_hunter]

create function testfunc(@testArg int)
returns @testResult int
as
begin
@testResult = @testArg;
RETURN
end
在C#中，SelectCommand.ExecuteScalar()可以得到返回值。

[chuxue1342]

我是要得到多个值.我先一个开始啊..多怕更多错!我试了上面的.还是报错:
Dim Connection As SqlConnection = cn
Dim cm As SqlCommand = New SqlCommand("pmc_count", cn)
cm.CommandType = CommandType.StoredProcedure
Dim para As New SqlParameter("@comtype", SqlDbType.VarChar, 50)
para.Value = comcount.Text
cm.Parameters.Add(para)
cm.ExecuteReader()
Dim count As Integer
count = cm.ExecuteScalar()
comcount.Text = count.ToString
cm.Dispose()

[chuxue1342]

已有打开的与此命令相关联的 DataReader，必须首先将它关闭!
-----------------------
可是我没用这个哦!

[babyrockxray]

cm.ExecuteReader()把这行注释
'cm.ExecuteReader()

[Tomming2002]

Dim Connection As SqlConnection = cn '确定cn打开
Dim cm As SqlCommand = New SqlCommand("pmc_count", cn)
cm.CommandType = CommandType.StoredProcedure
Dim para As New SqlParameter("@comtype", SqlDbType.VarChar, 50)
para.Value = comcount.Text
cm.Parameters.Add(para)
cm.ExecuteReader()'这个注释掉看看
Dim count As Integer
count = cm.ExecuteScalar()
comcount.Text = count.ToString
cm.Dispose()
--------------------------------
报啥错?

[chuxue1342]

晕了.不行!用select @comcount后台该怎么写呢?

[chuxue1342]

这个就是报错:
已有打开的与此命令相关联的 DataReader，必须首先将它关闭!

[Tomming2002]

给你多个结果集返回的参考
CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int
select @comcount=(select count(*) from pmc where comtype=@comtype)
select * from pmc
GO
======================================================================
Dim Connection As SqlConnection = cn
Dim cm As SqlCommand = New SqlCommand("pmc_count", cn)
cm.CommandType = CommandType.StoredProcedure

Dim para As New SqlParameter("@comtype", SqlDbType.VarChar, 50)
para.Value = comcount.Text
cm.Parameters.Add(para)

Dim apt As SqlDataAdapter = New SqlDataAdapter(cm)
Dim ds As DataSet = New DataSet
apt.Fill(ds)

Dim count As Integer
count = ds.Tables(0).Rows(0).Item(0)
'ds.Tables(1)为select语句"select * from pmc"返回的结果集

comcount.Text = count.ToString
cm.Dispose()

[Tomming2002]

好象是这样吧.你试试
CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int
select @comcount=(select count(*) from pmc where comtype=@comtype)
select @comcount
select * from pmc
GO

[Tomming2002]

存储过程里这个语句
select @comcount=(select count(*) from pmc where comtype=@comtype)
只是赋值,不是输出
select @comcount
才是输出

[chuxue1342]

下面这个可以了.谢谢!

[leixueqiyi]

CREATE PROCEDURE pmc_count(@comtype varchar(50))
as
declare @comcount int
set @comcount=(select count(*) from pmc where comtype=@comtype)
return @comcount
GO