存储过程sp
REA TE PROCEDURE sp
@dept varchar(10) ,
@sno varchar(10)
AS
SELECT s.sno, s.sname, ISNULL(A VG(grade),-1) average FROM s,sc
WHERE sdept=@dept and s.sno>=@sno and s.sno=sc.sno group by s.sno,s.sname
RETURN
GO
Dim cnn1 As ADODB.Connection '连接
Dim mycommand As mand '命令
Dim parm_dept As ADODB.Parameter '参数1
Dim parm_sno As ADODB.Parameter '参数2
Dim rstByQuery As ADODB.Recordset '结果集
Dim strCnn As String '连接字符串
Private Sub Command1_Click()
Dim i As Integer
Dim j As Integer
Set parm_dept = New ADODB.Parameter
Set mycommand = New mand
' parm_ = "name1"
parm_dept.Type = adChar '参数类型
parm_dept.Size = 10 '参数长度
parm_dept.Direction = adParamInput
'参数方向，输入或输出
parm_dept.V alue = "计科系" '参数的值
mycommand.Parameters.Append parm_dept '加入参数Set parm_sno = New ADODB.Parameter
'parm_ = "name2"
parm_sno.Type = adInteger
parm_sno.Size = 10
parm_sno.Direction = adParamInput
parm_sno.V alue = "1501"
mycommand.Parameters.Append parm_sno
mycommand.ActiveConnection = cnn1
'指定该command 的当前活动连接
mandText = "sp"
'myprocedure 是你要调用的存储过程名称mandType = adCmdStoredProc
'表明command 为存储过程
Set rstByQuery = New ADODB.Recordset
Set rstByQuery = mycommand.Execute()
MSFlexGrid1.Rows = 1
'动态设置MSFlexGrid的行和列
MSFlexGrid1.Cols = rstByQuery.Fields.Count MSFlexGrid1.Row = 0
For i = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = i
MSFlexGrid1.Text = rstByQuery.Fields.Item(i).Name Next '设置第一行的标题，用域名填充
i = 1
Do While Not rstByQuery.EOF
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.Row = i '确定行
For j = 0 To rstByQuery.Fields.Count - 1
MSFlexGrid1.Col = j
MSFlexGrid1.Text = rstByQuery(j)
'添充所有的列
Next
rstByQuery.MoveNext
i = i + 1
Loop
End Sub
Private Sub Command2_Click()
End
End Sub
Private Sub Form_Load()
Set cnn1 = New ADODB.Connection
'生成一个连接
strCnn = "DRIVER=SQL Server; SERVER=192.168.1.4; UID=sa; PWD=; DA TABASE=sample"
cnn1.Open strCnn '打开连接
End Sub
Private Sub Form_Unload(Cancel As Integer) cnn1.Close '关闭连接
Set cnn1 = Nothing '释放连接
End Sub。