1,216
社区成员
发帖
与我相关
我的任务
分享
Option Explicit
Private Sub Command1_Click()
Dim MyComm As New ADODB.Command
Dim Rs_GetList As New ADODB.Recordset
With MyComm
.ActiveConnection = Cn 'MarketConn是数据库连接字串
.CommandText = "pp(3)" '" 'SP_Ping_Dialtest" '指定存储过程名
.CommandType = adCmdStoredProc '表明这是一个存储过程
.Prepared = True '要求将SQL命令先行编译
' .Parameters(1) = "1"
Set Rs_GetList = .Execute
End With
'Set Rs_GetList = Cn.Execute("call pp()")
While Not Rs_GetList.EOF
Debug.Print Rs_GetList.Fields(0).Value
Rs_GetList.MoveNext
Wend
End Sub
上面是VB的代码。CN通过数据源和MYSQL联系起来
下面是存储过程的代码
[code=SQL]
DROP TABLE IF EXISTS `sys_dictionary`;
CREATE TABLE `sys_dictionary` (
`SYS_DICTIONARY_ID` int(8) NOT NULL,
`NAME` varchar(50) default NULL,
`REMARKS` varchar(1000) default NULL,
`PARENT_ID` int(8) default NULL,
`SORT` int(8) default NULL,
`ISFLAG` char(1) default NULL,
`SIGN` varchar(50) default NULL,
`SYS_VALUE` varchar(50) default '',
PRIMARY KEY (`SYS_DICTIONARY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
drop procedure IF EXISTS `pp`;
create procedure PP(a int)
begin
DECLARE v int;
set v=(select SUM(case when s1 >2 then 1 else 0 end )from t);
select * from t where s1>v;
end
Private Sub Command1_Click()
Dim MyComm As New ADODB.Command '定义一个命令对象
Dim Rs_GetList As New ADODB.Recordset '定义一个记录集对象
Dim param As ADODB.Parameter '定义一个参数对象
With MyComm
.ActiveConnection = Cn '数据连接
.CommandText = "pp" '存储过程名称
.CommandType = adCmdStoredProc '表明这是一个存储过程
.Prepared = True '要求将SQL命令先行编译
Set param = .CreateParameter("@a", adInteger, adParamInput, 4, 3) '申明一个参数
.Parameters.Append param '把参数添加到参数列表里
Set Rs_GetList = .Execute '执行此存储过程,返回记录集
End With
While Not Rs_GetList.EOF '循环打印记录集的字段
Debug.Print Rs_GetList.Fields(0).Value
Rs_GetList.MoveNext
Wend
End Sub
Dim param As ADODB.Parameter
Set param = cmd.CreateParameter("@a", adInteger, adParamInput, 4, 1)
cmd.Parameters.Append param