在VBA中调用的存储过程一直保持默认值参数
问题描述:
我有一个存储过程,我从Excel调用。其中一个参数是一个浮点数,它似乎没有进入SQL Server。结果在服务器上很好,当我在VBA中对查询进行硬编码时。我在SQL Server分析器中追踪到它,它传递给默认值,即用户没有指定的null。这里会发生什么?在VBA中调用的存储过程一直保持默认值参数
存储过程:
CREATE PROCEDURE um.topStudents
@semesterStart datetime,
@semesterEnd datetime,
@SchoolID AS char(10) = null,
@gradeThreshold AS float = null
AS
BEGIN
SELECT TOP 20
sc.schoolname AS School,
sd.Last, sd.First, sd.SSN, sd.DOB
FROM
dbo.StudentRecords sr
INNER JOIN
dbo.lkpmajor mj ON r.major = mj.code
INNER JOIN
dbo.tblstudDemo sd ON sr.SchoolID = sd.schoolid AND r.SSN = sd.SSN
INNER JOIN
dbo.tblschools sc ON sr.SchoolID = sc.code
WHERE
r.StartDate between @semesterStart AND @semesterEnd
AND ((sr.SchoolID = @SchoolID) OR (@SchoolID IS NULL))
AND sr.major = '03'
AND sr.program = 'scholarship'
AND ((sr.grade>@gradeThreshold) OR ((@gradeThreshold IS NULL) AND (gradeThreshold >=3.0)))
ORDER BY
sr.grade DESC;
END
的VBA:
Private Sub topStudents_Grades_Thres(semesterstart As String, semesterend As String, Optional ByVal gradethreshold As Double)
'New ADODB Connection
....
'New ADODB Command
'With ADODB Command
'use connection
'open stored procedure
.Parameters.Append cm_TopStud.CreateParameter("@semesterStart", adChar, adParamInput, 8, semesterstart)
.Parameters.Append cm_TopStud.CreateParameter("@semesterEnd ", adChar, adParamInput, 8, semesterend)
.Parameters.Append cm_TopStud.CreateParameter("@gradeThreshold", adDouble, adParamInput, gradethreshold)
.CommandTimeout = 300
End With
'Open command with recordset
'Set range & copy from recordset
'Close recordset & connection
End Sub
Call topStudents_Grades_Thres("20150701", "20160204", 2.75)
UPDATE:试图弄清楚为什么参数没有经历到SQL Server:
CREATE PROCEDURE [UM].[usp_Test]
@intParameter as int = null
as
BEGIN
IF (@intParameter IS NULL)
SELECT 1 + 0 AS [Test]
ELSE
SELECT 1 + @intParameter AS [Test]
END
未经过的电话号码:
Private Sub TestSub(Optional ByVal num As Integer)
....
.Parameters.Append cm_numTest.CreateParameter("@intParameter", adInteger, adParamInput, num)
....
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim num As Integer
num = CInt(Worksheets("Sheet1").Range("D1"))
If ActiveCell.Address = "$A$1" Then
Call TestSub(num)
End If
End Sub
答
您的VBA代码仅发送3个值(semesterStart,semesterEnd,gradeThreshold)作为SQL Procedure输入参数。 SQL过程期待4个输入参数;但是,缺少的输入参数(SchoolID)在SQL过程中被定义为默认NULL。
您必须将“.Parameters.Append cm_TopStud.CreateParameter(...)”添加到您的VBA以获得SchoolID,并将您的[Call topStudents_Grades_Thres(“20150701”,“20160204”,2.75)]修改为类似[假设“123456”是学校ID,请致电topStudents_Grades_Thres(“20150701”,“20160204”,“123456”,2.75)]。
答
谢谢!我得到它的工作!更改为正确的数据类型也改变从这个创建追加参数行:
.Parameters.Append cm_TopStud.CreateParameter("@semesterStart", adChar, adParamInput, 8,
semesterstart)
这样:
Dim param_semesterStart As ADODB.Parameter
Set param_semesterStart = New ADODB.Parameter
With cm_TopStud
....
Set param_semesterStart= .CreateParameter("@semesterStart", adDBDate, adParamInput)
param_semesterStart.Value = semesterstart
.Parameters.Append param_semesterStart
....
End With
也堵到“黑客帝国”帮助和:P
展望在例子中,你可能不应该在参数 –
的名称中包含@ @ .Parameters.Append cm_TopStud.CreateParameter(“gradeThreshold”,adDouble,adParamInput,gradethreshold) – Chris
我看到@ProvID引用在您的SP,但没有定义为参数。 – PKatona