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＃调用存储过程”的代码有错误，但是将参数直接写进存储过程后，还是不对。

[jzywh]

Show the sp "sp_creatMonthKCReport"....

[xin_shui]

[Quote=引用楼主 user54 的帖子:]
怀疑是“c＃调用存储过程”的代码有错误，但是将参数直接写进存储过程后，还是不对。
[/Quote]

什么意思？是不是存储过程里sql的问题

[uyou]

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

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

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

[xiaoqhuang]

如果参数传对了,C#执行存储过程跟查询分析器里执行结果是完全一样的.

[uyou]

我把参数固定的写在了存储过程，结果还是那样。没招了。

[xin_shui]

会不会是权限原因？

[粉红色的火烈鸟]

SqlParameter sp_1 = new SqlParameter("@ny", SqlDbType.VarChar, 7);

SqlDbType.VarChar换成SqlDbType.NvarChar试试看

[roycelwh]

该存储过程在查询分析器执行结果如下(正确)：
id value
01 aaa
02 bbb
03 ccc
在页面调用后结果如下（错误）：
id value
01 aaa
02 aaa
03 aaa

你查询分析器里的参数值与页面里的不一样吧,不然怎么会这样

[meitingting]

我的程序也遇到和你类似的问题了,我在查询分析器下执行得到的结果完全正确,但是在C#下就不行,好象是数据类型的问题,大哥你要是解决了把原因给说说噢,谢谢了