求救,一个存储过程在程序中不知道怎么就错了?
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
——————————————————