在asp.net中存储过程 [英] Store procedure in asp.net
问题描述
我创建了以下商店程序。但是当我跑步时,就会出现错误。错误消息是:
消息156,级别15,状态1,过程sp_ManpowerCollection_TEST,第7行
关键字'select'附近的语法不正确。
消息102,级别15,状态1,过程sp_ManpowerCollection_TEST,第7行
')'附近的语法不正确。
消息156,级别15,状态1,过程sp_ManpowerCollection_TEST,第15行
关键字'else'附近的语法不正确。
请帮助我。我可以在其他部分使用c#代码等消息吗?
创建proc [dbo]。[sp_ManpowerCollection_TEST]
@ ATTFromDate datetime,
@ATTToDate datetime,
@Location nvarchar(50)
as
开始
if(不存在)从dbo.tblBacManpoweCollection中选择COUNT(*),其中ATTdate位于'@ ATTFromDate'和'@ ATTToDate'之间,位置='@ Location')
开始
SELECT EmpCode,ATTdate,LineCode,LineName,DesigCode,Designation,CompanyName,UnitName,DeptName,SecName,InHour,
InMin,OutHour,OutMin,Location,Counter,TotalOTHour,DayFlag,ProcessTime FROM tblManpowerCollection WHERE ATTdate在'@ ATTFromDate'和'@ ATTToDate'和Location ='@ Location'之间
删除tblManpowerCollection,其中ATTdate位于'@ ATTFromDate'和'@ ATTToDate'之间,Location ='@ Location'
结束
其他
开始
返回'假'
结束
结束
你不需要围绕SELECT的括号吗?
IF NOT EXISTS (< span class =code-keyword> SELECT COUNT(*)...而不是
IF ( NOT EXISTS SELECT COUNT(*)...
你犯了几个错误。首先咨询EXISTS
语法: http:// technet.microsoft.com/en-us/library/ms188336.aspx [ ^ ]
1)count(*)
将始终返回一行,因此EXISTS
将永远为真。只需使用*
。
2)EXISTS
就像一个功能,你需要括号:if(not exists(select * from db ...)...
SP中存在语法错误代码。试试这个提示
创建 proc [dbo]。[sp_ManpowerCollection_TEST]
(
@ ATTFromDate datetime ,
@ ATTToDate datetime ,
< span class =code-sdkkeyword> @ Location nvarchar ( 50 )
)
as
开始
if (不 存在 选择 CO UNT(*)来自 dbo.tblBacManpoweCollection 其中 ATTdate @ ATTFromDate 和 @ ATTToDate 和位置= @位置)
开始
delete tblManpowerCollection 其中 ATTdate @ ATTFromDate 和 @ ATTToDate 和位置= @位置
SELECT EmpCode,ATTdate,LineCode,LineName,DesigCode,Designation,CompanyName,UnitName,DeptName ,SecName,InHour,
InMin,OutHour,OutMin,Location,Counter,TotalOTHour,DayFlag,ProcessTime FROM tblManpowerCollection WHERE ATTdate @ ATTFromDate 和 @ ATTToDate 和位置= @位置
结束
端跨度>
I created the following store procedure. But when I run, it arises error. The error message is :
"Msg 156, Level 15, State 1, Procedure sp_ManpowerCollection_TEST, Line 7
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure sp_ManpowerCollection_TEST, Line 7
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Procedure sp_ManpowerCollection_TEST, Line 15
Incorrect syntax near the keyword 'else'."
Please help me. Can I use message like c# code in else portion?
Create proc [dbo].[sp_ManpowerCollection_TEST]
@ATTFromDate datetime,
@ATTToDate datetime,
@Location nvarchar(50)
as
Begin
if(not exists select COUNT(*) from dbo.tblBacManpoweCollection where ATTdate between '@ATTFromDate' and '@ATTToDate' and Location='@Location')
begin
SELECT EmpCode, ATTdate, LineCode,LineName,DesigCode,Designation,CompanyName,UnitName,DeptName,SecName,InHour,
InMin,OutHour,OutMin,Location,Counter,TotalOTHour,DayFlag,ProcessTime FROM tblManpowerCollection WHERE ATTdate between '@ATTFromDate' and '@ATTToDate' and Location='@Location'
delete tblManpowerCollection where ATTdate between '@ATTFromDate' and '@ATTToDate' and Location='@Location'
end
else
begin
Return 'False'
end
End
Don't you need brackets round the SELECT?
IF NOT EXISTS (SELECT COUNT(*)...Instead of
IF (NOT EXISTS SELECT COUNT(*)...
You made several mistakes. First of all consultEXISTS
syntax: http://technet.microsoft.com/en-us/library/ms188336.aspx[^]
1)count(*)
will always return a row, thusEXISTS
will always be true. Simply use*
instead.
2)EXISTS
is like a "function", you need parenthesis:if(not exists (select * from db...)...
There are syntax error in your SP Code. Try this for a hint
Create proc [dbo].[sp_ManpowerCollection_TEST] ( @ATTFromDate datetime, @ATTToDate datetime, @Location nvarchar(50) ) as Begin if(not exists select COUNT(*) from dbo.tblBacManpoweCollection where ATTdate between @ATTFromDate and @ATTToDate and Location=@Location) begin delete tblManpowerCollection where ATTdate between @ATTFromDate and @ATTToDate and Location=@Location SELECT EmpCode, ATTdate, LineCode,LineName,DesigCode,Designation,CompanyName,UnitName,DeptName,SecName,InHour, InMin,OutHour,OutMin,Location,Counter,TotalOTHour,DayFlag,ProcessTime FROM tblManpowerCollection WHERE ATTdate between @ATTFromDate and @ATTToDate and Location=@Location end end
这篇关于在asp.net中存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!