c#执行sql存储过程，出了怪事。专家指教 在线等

[uyou]

c＃调用存储过程如下：
string strcon;
strcon = Code.Configuration.CONNECTIONSTRING;
conn = new SqlConnection(strcon);
conn.Open();
SqlCommand cmd = new SqlCommand("sp_creatMonthKCReport", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sp_1 = new SqlParameter("@ny", SqlDbType.VarChar, 7);
sp_1.Value ="2008-06";
cmd.Parameters.Add(sp_1);
cmd.ExecuteNonQuery();
conn.Close();


该存储过程在查询分析器执行结果如下(正确)：
id value
01 aaa
02 bbb
03 ccc
在页面调用后结果如下（错误）：
id value
01 aaa
02 aaa
03 aaa
怀疑是“c＃调用存储过程”的代码有错误，但是将参数直接写进存储过程后，还是不对。


该存储过程在查询分析器执行结果完全正确

出错的代码段：
--本月结存

DECLARE currMonthStore_cursor CURSOR FOR
select x.itembh,( case when x.buyTotalNums is null then 0-y.sellTotalNums else x.buyTotalNums-y.sellTotalNums end) as currMonthStore
from (select a.itembh,b.buyTotalNums,b.buyTotalMoney
from tmp_kctj1 a
left join (select itembh,
sum(innum) as buyTotalNums, sum(inprice) as buyTotalMoney
from dict_kcjh_item
where '20'+substring(incode,3,2)+'-'+substring(incode,5,2) <=@currYearMonth
group by itembh) b
on ( a.itembh=b.itembh)) x
left join (select a.itembh,b.sellTotalNums,b.sellTotalMoney from tmp_kctj1 a
left join (select y_a.clsbh,y_a.itembh,sum(y_a.num) as sellTotalNums,sum(y_a.subtotal) as sellTotalMoney
from Buz_ScheduleDetail y_a left join Buz_ScheduleCard y_b on y_a.bh=y_b.bh
where convert(char(7),y_b.ChargeDate,120) <=@currYearMonth
group by y_a.clsbh,y_a.itembh) b on ( a.itembh=b.itembh)) y
on x.itembh=y.itembh

OPEN currMonthStore_cursor

FETCH NEXT FROM currMonthStore_cursor
INTO @sp_buyItembh,@currMonthStore

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @sp_buyItembh

--PRINT '售价'+@sp_buyUnitPrice

update tmp_kctj1 set currMonthStore=@currMonthStore
where itembh=@sp_buyItembh

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM currMonthStore_cursor
INTO @sp_buyItembh,@currMonthStore

END

CLOSE currMonthStore_cursor
DEALLOCATE currMonthStore_cursor

--------------------------------------

下面这段也是该存储过程的内容，但是没有出错。
--将本月采购信息更新至统计临时统计表

DECLARE buy_cursor CURSOR FOR
select a.itembh,b.buyTotalNums,b.buyunitprice from tmp_kctj1 a
left join (select itembh,'20'+substring(incode,3,2)+'-'+substring(incode,5,2) as ny,
sum(innum) as buyTotalNums,-- sum(inprice) as buyTotalMoney,
(case when (sum(innum) is null or sum(innum)=0) then '' else sum(innum*inunitprice)/sum(innum) end) as buyunitprice
from dict_kcjh_item
group by '20'+substring(incode,3,2)+'-'+substring(incode,5,2),itembh) b on (a.ny=b.ny and a.itembh=b.itembh)


OPEN buy_cursor

FETCH NEXT FROM buy_cursor
INTO @sp_buyItembh,@currMonthTotalBuyNums,@sp_buyUnitPrice

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @sp_buyItembh

--PRINT '售价'+@sp_buyUnitPrice

update tmp_kctj1 set currMonthBuyNums=@currMonthTotalBuyNums,buyunitprice=@sp_buyUnitPrice
where itembh=@sp_buyItembh and ny=@currYearMonth

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM buy_cursor
INTO @sp_buyItembh,@currMonthTotalBuyNums,@sp_buyUnitPrice
END

CLOSE buy_cursor
DEALLOCATE buy_cursor

[caofan520]

事件探测器看下是怎么执行存储的

然后把执行存储放到查询分析器执行下就知道什么原因了

[chengqscjh]

该存储过程在查询分析器执行结果完全正确
----------------------------------
说明存储过程的问题不大

在页面调用后结果如下（错误）：
--------------------------
是否你绑定数据源循环遍历时出错?可以跟踪下

[lsj_zrp]

调试看一下你获得的数据集是否正确，如果正确看看你绑定数据源的时候是否出了什么问题

[xiaoqhuang]

把C#执行的语句直接放查询分析器里执行就知道问题在哪了
用Profiler获取执行语句

查询分析器->工具->Sql Server Profiler->File->New Trace->连接到一个数据库之后-->Use the template选TSQL_Duration(选这个比较简洁,并可以看sql的执行时间和sql脚本.)-->再点Events Selection勾上TextData-->Run....

[xxoo2007]

如果在查询分析器是对的,那就不是存储过程的问题. 检查数据的展示部分代码,可能是写代码的时候,copy /paste代码产生了错误.

[uyou]

exec sp_creatMonthKCReport @ny = '2008-06'
以上查询分析器执行，在表里查看数据有错（只有2个字段错，）。

以下查询分析器执行，在表里查看数据没错。（超长了，只放部分代码）
declare @currYearMonth [varchar] (200)
declare @sp_Itembh [varchar] (200)

declare @sp_buyClassName [varchar] (200)
declare @sp_buyclsbh [varchar] (200)
declare @sp_buyitemname [varchar] (200)
declare @sp_buyItembh [varchar] (200)
declare @sp_sellItembh [varchar] (200)
declare @sp_buyrq [varchar] (200)
declare @sp_buyKcGx [varchar] (200)
declare @sp_buyKcBz [varchar] (200)
declare @sp_buyKcDx [varchar] (200)
declare @sp_buymanufacturer [varchar] (200)
declare @sp_buyTotalNum [varchar] (200)
declare @sp_buyTotalMoney [varchar] (200)
declare @sp_sellTotalNum [varchar] (200)
declare @sp_sellTotalMoney [varchar] (200)
declare @sp_buyUnitPrice [varchar] (200)
declare @sp_SellUnitPrice [varchar] (200)
declare @sp_tmpStatus [int]

declare @currMonthTotalBuyNums [varchar] (200)
declare @currMonthTotalSellNums [varchar] (200)
declare @prevMonthTotalBuyNums [varchar] (200)
declare @prevMonthTotalSellNums [varchar] (200)
declare @currMonthStore [varchar] (200)
declare @prevMonthStore [varchar] (200)

select @currYearMonth='2008-06'--@ny

--创建临时表开始,
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_kctj1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table [dbo].[tmp_kctj1]
end
CREATE TABLE [dbo].[tmp_kctj1] (
[classname] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[ClsBh] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[ItemName] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[itembh] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[KcGx] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[KcBz] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[KcDx] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[manufacturer] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[ny] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[sellTotalNum] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[sellTotalMoney] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[sellUnitPrice] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[buyTotalNum] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[buyTotalMoney] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[buyUnitPrice] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[preMonthStore] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[currMonthBuyNums] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[currMonthSellNums] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[currMonthStore] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

----------------------------

--本月 依据进行库存管理的商品插入临时统计表，日期字段填入条件年月

insert tmp_kctj1 (itembh,ClsBh,ItemName,sellUnitPrice,KcGx, KcBz, KcDx,manufacturer, classname,ny)
(select a.ItemBh, a.ClsBh, a.ItemName,
a.UnitPrice, a.KcGx, a.KcBz, a.KcDx, a.ghs ,b.classname,@currYearMonth
from dict_item a left join dict_class b on (a.clsbh=b.clsbh)
where a.kcgx<>0 and a.kcbz<>0 and a.kcdx<>0)

----------------------------

--上月结存(更新的结果错了，像是游标没有循环，奇怪的是编号循环了。)

DECLARE prevMonthStore_cursor CURSOR FOR
select x.itembh,( case when x.buyTotalNums is null then 0-y.sellTotalNums else x.buyTotalNums-y.sellTotalNums end) as prevMonthStore
from (select a.itembh,b.buyTotalNums,b.buyTotalMoney
from tmp_kctj1 a
left join (select itembh,
sum(innum) as buyTotalNums, sum(inprice) as buyTotalMoney
from dict_kcjh_item
where '20'+substring(incode,3,2)+'-'+substring(incode,5,2)<=convert(char(7),dateadd(month,-1,@currYearMonth+'-01'),120)
group by itembh) b
on ( a.itembh=b.itembh)) x
left join (select a.itembh,b.sellTotalNums,b.sellTotalMoney from tmp_kctj1 a
left join (select y_a.clsbh,y_a.itembh,sum(y_a.num) as sellTotalNums,sum(y_a.subtotal) as sellTotalMoney
from Buz_ScheduleDetail y_a left join Buz_ScheduleCard y_b on y_a.bh=y_b.bh
where convert(char(7),y_b.ChargeDate,120)<=convert(char(7),dateadd(month,-1,@currYearMonth+'-01'),120)
group by y_a.clsbh,y_a.itembh) b on ( a.itembh=b.itembh)) y
on x.itembh=y.itembh

OPEN prevMonthStore_cursor

FETCH NEXT FROM prevMonthStore_cursor
INTO @sp_Itembh,@prevMonthStore

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @sp_buyItembh
--select @prevMonthStore='0'
--PRINT '售价'+@sp_buyUnitPrice
--insert tmp_kctj11 (ClsBh,Classname) values (@sp_buyItembh,@prevMonthStore)
update tmp_kctj1 set preMonthStore=@prevMonthStore
where itembh=@sp_Itembh

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM prevMonthStore_cursor
INTO @sp_Itembh,@prevMonthStore
END

CLOSE prevMonthStore_cursor
DEALLOCATE prevMonthStore_cursor

--------------------------------------

--将本月采购信息更新至统计临时统计表（没错）

DECLARE buy_cursor CURSOR FOR
select a.itembh,b.buyTotalNums,b.buyunitprice from tmp_kctj1 a
left join (select itembh,'20'+substring(incode,3,2)+'-'+substring(incode,5,2) as ny,
sum(innum) as buyTotalNums,-- sum(inprice) as buyTotalMoney,
(case when (sum(innum) is null or sum(innum)=0) then '' else sum(innum*inunitprice)/sum(innum) end) as buyunitprice
from dict_kcjh_item
group by '20'+substring(incode,3,2)+'-'+substring(incode,5,2),itembh) b on (a.ny=b.ny and a.itembh=b.itembh)



OPEN buy_cursor

FETCH NEXT FROM buy_cursor
INTO @sp_buyItembh,@currMonthTotalBuyNums,@sp_buyUnitPrice

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @sp_buyItembh

--PRINT '售价'+@sp_buyUnitPrice

update tmp_kctj1 set currMonthBuyNums=@currMonthTotalBuyNums,buyunitprice=@sp_buyUnitPrice
where itembh=@sp_buyItembh and ny=@currYearMonth

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM buy_cursor
INTO @sp_buyItembh,@currMonthTotalBuyNums,@sp_buyUnitPrice
END

CLOSE buy_cursor
DEALLOCATE buy_cursor
------------------------------------

[uyou]

测试结果：
exec sp_creatMonthKCReport @ny = '2008-06'

（所影响的行数为 4 行）

item0054
-43

（所影响的行数为 1 行）

item0055
-43

（所影响的行数为 1 行）

item0288
-43

（所影响的行数为 1 行）

item0289
-43


把存储过程代码放在分析器执行
所影响的行数为 4 行）

item0054
-43

（所影响的行数为 1 行）

item0055
-7

（所影响的行数为 1 行）

item0288
-4204

（所影响的行数为 1 行）

item0289
-630


出问题的游标单独执行结果如下
item0288 -4204
item0054 -43
item0289 -630
item0055 -7