• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

求救,一个存储过程在程序中不知道怎么就错了?

qozm 2004-02-24 03:47:35
我写了一个存储过程在查询分析器里面用的好好的没问题,
我用vb编码就出错,搞不懂怎么回事?
有没有什么方法可以查看由存储过程生成的sql语句?



把存储过程中的 几个"like"换成"="的话就能正确执行,
但是数据库记录所限一定要用like,因为有 1,2,3之类的数据,
不知道是哪里出了问题了


——————————————
存储过程

CREATE PROC proc_getClassroomOccupyStat2
@flag varchar(1)='1',
@tc_version varchar(9),
@weekly_num int,
@day_num smallint,
@hour_num_list varchar(20),
@occupy_type tinyint,
@building_name varchar(10),
@use_object varchar(50)
AS
BEGIN
IF(@flag='1')
BEGIN
SELECT count(*) as returnCount FROM classroom_rent_info
WHERE version = @tc_version
AND ((start_week<=@weekly_num and end_week>=@weekly_num) and (occupy_type=@occupy_type or occupy_type=0))
AND day_num=@day_num
AND hour_num_list like @hour_num_list
AND rc_id in (select classroom_id from base_classroom where building_name=@building_name)
AND use_object=@use_object
END
ELSE
BEGIN
SELECT count(*) as returnCount FROM classroom_rent_info
WHERE version = @tc_version
AND ((start_week<=@weekly_num and end_week>=@weekly_num) and (occupy_type=@occupy_type or occupy_type=0))
AND day_num=@day_num
AND hour_num_list like @hour_num_list
AND rc_id in (select classroom_id from base_classroom where building_name=@building_name)
AND use_object not in ('本科生','研究生','成教学生') --为了查找除了三者之外的其他被占用信息--select * from classroom_rent_info where use_object not in ('本科生','研究生','成教学生')
END
END

GO

________________________
vb代码如下:
___________________________
Function proc_getClassroomOccupyStat2(flag, tc_version, weekly_num, day_num, hour_num_list, occupy_type, building_name, use_object, dnsStr)
Dim conn As ADODB.Connection
Dim cm As ADODB.Command
Dim rs As ADODB.Recordset


'参数
Dim prmFlag As ADODB.Parameter
Dim prmTc_version As ADODB.Parameter
Dim prmWeekly_num As ADODB.Parameter
Dim prmDay_num As ADODB.Parameter
Dim prmHour_num_list As ADODB.Parameter
Dim prmOccupy_type As ADODB.Parameter
Dim prmBuilding_name As ADODB.Parameter
Dim prmUse_object As ADODB.Parameter


Dim returnResult As Integer '保存记录条数的结果


Set conn = CreateObject("adodb.connection")
conn.Open dnsStr
Set cm = CreateObject("adodb.command")


Set prmFlag = cm.CreateParameter("flag", adChar, adParamInput, 1)
Set prmTc_version = cm.CreateParameter("tc_version", adChar, adParamInput, 9)
Set prmWeekly_num = cm.CreateParameter("weekly_num", adInteger, adParamInput)
Set prmDay_num = cm.CreateParameter("day_num", adInteger, adParamInput)
Set prmHour_num_list = cm.CreateParameter("hour_num_list", adChar, adParamInput, 20)
Set prmOccupy_type = cm.CreateParameter("occupy_type", adInteger, adParamInput)
Set prmBuilding_name = cm.CreateParameter("building_name", adChar, adParamInput, 10)
Set prmUse_object = cm.CreateParameter("use_object", adChar, adParamInput, 50)

prmFlag.value = CStr(flag)
prmTc_version.value = CStr(tc_version)
prmWeekly_num.value = CInt(weekly_num)
prmDay_num.value = CInt(day_num)
prmHour_num_list.value = CStr(hour_num_list)
prmOccupy_type.value = CInt(occupy_type)
prmBuilding_name.value = CStr(building_name)
prmUse_object.value = CStr(use_object)

cm.Parameters.Append prmFlag
cm.Parameters.Append prmTc_version
cm.Parameters.Append prmWeekly_num
cm.Parameters.Append prmDay_num
cm.Parameters.Append prmHour_num_list
cm.Parameters.Append prmOccupy_type
cm.Parameters.Append prmBuilding_name
cm.Parameters.Append prmUse_object

Set cm.ActiveConnection = conn
cm.CommandText = "proc_getClassroomOccupyStat2"
cm.CommandType = adCmdStoredProc

Set rs = CreateObject("adodb.recordset")
Set rs = cm.Execute()

'''' Dim x
'''' x = rs.recordCount '这样写为什么会出错?没有得到应该返回的值

returnResult = CInt(Trim(rs("returnCount").value))

proc_getClassroomOccupyStat2 = returnResult

Set conn = Nothing
Set cm = Nothing
Set rs = Nothing

Set prmFlag = Nothing
Set prmTc_version = Nothing
Set prmWeekly_num = Nothing
Set prmDay_num = Nothing
Set prmHour_num_list = Nothing
Set prmOccupy_type = Nothing
Set prmBuilding_name = Nothing
Set prmUse_object = Nothing
End Function


——————————————————
...全文
46 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复

还没有回复,快来抢沙发~

相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2004-02-24 03:47
社区公告
暂无公告