我写的存储过程,在数据库直接执行没有问题,但是在程序中调用就提示
“必须声明标量变量 "@total"。
必须声明标量变量 "@knowledge"。
必须声明标量变量 "@skill"。
必须声明标量变量 "@brainpowe"。
必须声明表变量 "@table"。”
为什么呀?求教高手
我是这么调用的 参数都不为空 public static bool GetAllDisponseResult(string knowledge, string skill, string brainpower, string total, string table, string upKnowledge, string upSkill, string upBrainpower, string upTotal)
{
string sql = "EXEC proc_standard '@knowledge','@skill','@brainpower','@total','@table','@upknowledge','@upskill','@upbrainpower','@uptotal'"; try
{ SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@knowledge",knowledge),
new SqlParameter("@skill",skill ),
new SqlParameter("@brainpower", brainpower),
new SqlParameter("@total", total),
new SqlParameter("@table", table),
new SqlParameter("@upknowledge",upKnowledge),
new SqlParameter("@upskill", upSkill),
new SqlParameter("@upbrainpower", upBrainpower),
new SqlParameter("@uptotal", upTotal)
}; return 0 != DBHelper.ExecuteCommand(sql, para);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
}
“必须声明标量变量 "@total"。
必须声明标量变量 "@knowledge"。
必须声明标量变量 "@skill"。
必须声明标量变量 "@brainpowe"。
必须声明表变量 "@table"。”
为什么呀?求教高手
我是这么调用的 参数都不为空 public static bool GetAllDisponseResult(string knowledge, string skill, string brainpower, string total, string table, string upKnowledge, string upSkill, string upBrainpower, string upTotal)
{
string sql = "EXEC proc_standard '@knowledge','@skill','@brainpower','@total','@table','@upknowledge','@upskill','@upbrainpower','@uptotal'"; try
{ SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@knowledge",knowledge),
new SqlParameter("@skill",skill ),
new SqlParameter("@brainpower", brainpower),
new SqlParameter("@total", total),
new SqlParameter("@table", table),
new SqlParameter("@upknowledge",upKnowledge),
new SqlParameter("@upskill", upSkill),
new SqlParameter("@upbrainpower", upBrainpower),
new SqlParameter("@uptotal", upTotal)
}; return 0 != DBHelper.ExecuteCommand(sql, para);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
}
解决方案 »
- 解释一条SQL语句
- 哪有ASP.net做的进销存管理系统
- 为什么我新建web项目的时候只能建立在wwwroot下面?建立在别的地方就报"找不到web服务器的asp.net版本" 这个错误?
- 分享经验,做一个好用扩展性好的网站管理系统
- 请问如何将ASP.NET开发的程序打包
- 在.net里的HTML代码区进不去
- 菜鸟级问题:怎样在三层体系内调用它们之间的类?
- ASP.NET创建项目问题
- [CSDN首发]CSDN人回馈CSDN社区. 发布开源项目 aspnetcdn 第一个版本源代码. (ASP.NET做的CDN,为教学而生)
- 求救!我的web项目怎么突然打不开了!下面是错误信息:
- 大方点,散20分,那个vs2008功能在哪设置
- asp.net 数字证书登录 问题
if exists (select * from sysobjects where name='proc_standard')
drop procedure proc_standard
go
/*创建存储过程*/
create procedure proc_standard
/*参数*/
@knowledge varchar(10), --知识原始分
@skill varchar(10), --技能原始分
@brainpower varchar(10), --能力倾向(智能)原始分
@total varchar(10) , --总成绩原始分
@searchTable varchar(50), --要查询的表
@upknowledge varchar(10), --要更新的知识列
@upskill varchar(10), --要更新的技能列
@upbrainpower varchar(10), --要更新的能力(智能)列
@uptotal varchar(10) --要更新的总成绩列
as
declare
@knowledgeColumn varchar(10) --知识原始分列
set @knowledgeColumn=@knowledge
declare
@skillColumn varchar(10) --技能原始分列
set @skillColumn=@skill
declare
@brainpowerColumn varchar(10) --能力倾向(智能)原始分列
set @brainpowerColumn=@brainpower
declare
@totalColumn varchar(10) --总成绩原始分列
set @totalColumn=@totaldeclare
@totalAvg float, --平均分
@knowledgeAvg float, --知识平均分
@skillAvg float, --技能平均分
@brainpowerAvg float, --智能平均分@totalStdev float, --总成绩标准差
@knowledgeStdev float, --知识标准差
@skillStdev float, --技能标准差
@brainPowerStdev float --智能标准差declare @sqlTotal nvarchar(1000) --声明一条sql语句
set @sqlTotal='select @totalAvg=SUM('+@totalColumn+')/NULLIF(COUNT(1),0) from '+@searchTable+''
EXEC SP_EXECUTESQL @sqlTotal,N'@totalAvg float OUTPUT',@totalAvg=@totalAvg OUTPUTdeclare @sqlKnowledge nvarchar(1000)
set @sqlKnowledge='select @knowledgeAvg=SUM('+@knowledgeColumn+')/NULLIF(COUNT(1),0) from '+@searchTable+''
EXEC SP_EXECUTESQL @sqlKnowledge,N'@knowledgeAvg float OUTPUT',@knowledgeAvg=@knowledgeAvg OUTPUTdeclare @sqlSkill nvarchar(1000)
set @sqlSkill='select @skillAvg=SUM('+@skillColumn+')/NULLIF(COUNT(1),0) from '+@searchTable+''
EXEC SP_EXECUTESQL @sqlSkill,N'@skillAvg float OUTPUT',@skillAvg=@skillAvg OUTPUTdeclare @sqlBrain nvarchar(1000)
set @sqlBrain='select @brainpowerAvg=SUM('+@brainpowerColumn+')/NULLIF(COUNT(1),0) from '+@searchTable+''
EXEC SP_EXECUTESQL @sqlBrain,N'@brainpowerAvg float OUTPUT',@brainpowerAvg=@brainpowerAvg OUTPUT/*循环计算、插入数据*/
declare @i int,@pKnowlege float, --知识百分位
@pSkill float, --技能百分位
@pBrainpower float, --智能百分位
@pTotal float, --总成绩百分位@sKnowlege float , --知识标准分
@sSkill float , --技能标准分
@sBrainpower float , --智能标准分
@sTotal float --总成绩标准分declare @calcKnowledge nvarchar(1000),
@calcSkill nvarchar(1000),
@calcBrainPower nvarchar(1000),
@calcTotal nvarchar(1000),
@updataScore nvarchar(1000),
@SQL NVARCHAR(1000),
@ParmDefinition nvarchar(1000)
CREATE TABLE #TEMP_IDLIST (ID INT) SET @SQL='INSERT INTO #TEMP_IDLIST SELECT ID FROM '+@searchTable
SET @ParmDefinition=''
EXEC SP_EXECUTESQL @SQL,@ParmDefinitionDECLARE ID_CURSOR CURSOR SCROLL
FOR
(SELECT * FROM #TEMP_IDLIST)
Open ID_CURSOR
FETCH NEXT FROM ID_CURSOR INTO @i --游标赋给变量
WHILE(@@FETCH_STATUS=0) --如果游标存在
BEGIN
set @calcKnowledge=''
set @calcSkill=''
set @calcBrainPower=''
set @calcTotal=''
set @updataScore=''
SET @ParmDefinition=''
SET @sKnowlege=0
SET @sSkill=0
SET @sBrainpower=0
SET @sTotal=0
set @calcKnowledge=N'SELECT @sKnowlege=((select '+@knowledge+' from '+@searchTable+' where ID=@i)-@knowledgeAvg)
*16/STDEV('+@knowledge+')+100
from '+@searchTable
SET @ParmDefinition=N'@i INT,@knowledgeAvg FLOAT,@sKnowlege FLOAT OUTPUT'
exec SP_EXECUTESQL @calcKnowledge,@ParmDefinition,@i,@knowledgeAvg,@sKnowlege OUTPUT
SET @ParmDefinition=''
set @calcSkill=N'SELECT @sSkill=((select '+@skill+' from '+@searchTable+' where ID=@i)-@skillAvg)
*16/STDEV('+@skill+')+100
from '+@searchTable
SET @ParmDefinition=N'@i INT,@skillAvg FLOAT,@sSkill FLOAT OUTPUT'
exec SP_EXECUTESQL @calcSkill,@ParmDefinition,@i,@skillAvg,@sSkill OUTPUT
SET @ParmDefinition=''
set @calcBrainPower=N' SELECT @sBrainpower=((select '+@brainpower+' from '+@searchTable+' where ID=@i)-@brainpowerAvg)
*16/STDEV('+@brainpower+')+100
from '+@searchTable
SET @ParmDefinition=N'@i INT,@brainpowerAvg FLOAT,@sBrainpower FLOAT OUTPUT'
exec SP_EXECUTESQL @calcBrainPower,@ParmDefinition,@i,@brainpowerAvg,@sBrainpower OUTPUT
SET @ParmDefinition=''
set @calcTotal=N' SELECT @sTotal=((select '+@total+' from '+@searchTable+' where ID=@i)-@totalAvg)
*16/STDEV('+@total+')+100
from '+@searchTable
SET @ParmDefinition=N'@i INT,@totalAvg FLOAT,@sTotal FLOAT OUTPUT'
exec SP_EXECUTESQL @calcTotal,@ParmDefinition,@i,@totalAvg,@sTotal OUTPUT SET @ParmDefinition=''
set @updataScore=N' update '+@searchTable+'
set '+@upknowledge+'=@sKnowlege
,'+@upskill+'=@sSkill
,'+@upbrainpower+'=@sBrainpower
,'+@uptotal+'=@sTotal
where ID=@i'
SET @ParmDefinition=N'@sKnowlege FLOAT,@sSkill FLOAT,@sBrainpower FLOAT,@sTotal FLOAT,@i INT'
exec SP_EXECUTESQL @updataScore,@ParmDefinition,@sKnowlege,@sSkill,@sBrainpower,@sTotal,@i FETCH NEXT FROM ID_CURSOR INTO @i --跳到下个游标
END
CLOSE ID_CURSOR
DEALLOCATE ID_CURSOR --释放游标 DROP TABLE #TEMP_IDLIST
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}