How do you pass parameters to a stored procedure

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I am trying to call a stored procedure from Access, but I am having problems
with optional parameters. If I execute the stored procedure from the SQL Mgmt
console it executes fine and inserts data into the table. When I try the same
thing from my VBA code no data is inserted. Here is the VBA code:

Public Sub Create_ScoreCard_Report_Data(ByVal FiscalYear As String, _
ByVal UserName As String, _
Optional ByVal PhysicianId As String =
"", _
Optional ByVal Department As String =
"", _
Optional ByVal Division As String = "", _
Optional ByVal Speciality As String = "")
'@FiscalYear AS NVARCHAR(4),
'@UserName as nvarchar(20),
'@PhysicianId AS NVARCHAR(255)= NULL,
'@Department AS NVARCHAR(255)= NULL,
'@Division AS NVARCHAR(10) = NULL,
'@Speciality AS NVARCHAR(255) = NULL --,

On Error GoTo Error_Create_ScoreCard_Report_Data

Set adoCmd = New ADODB.Command
Set adoParam = New ADODB.Parameter
Set adoRS = New ADODB.Recordset


With adoCmd
'set the connection last
Set .ActiveConnection = CurrentProject.Connection
'create the return parameter
'Set adoParam = .CreateParameter("@RecordsCreated", adInteger,
adParamReturnValue)
'.Parameters.Append adoParam
'create and append the input parameters
Set adoParam = .CreateParameter("@FiscalYear", adVarChar,
adParamInput, Len(FiscalYear), FiscalYear)
.Parameters.Append adoParam
Set adoParam = .CreateParameter("@UserName", adVarChar,
adParamInput, Len(UserName), UserName)
.Parameters.Append adoParam
If Len(PhysicianId) > 0 Then
Set adoParam = .CreateParameter("@PhysicianId", adVarChar,
adParamInput, Len(PhysicianId), PhysicianId)
.Parameters.Append adoParam
End If
If Len(Department) > 0 Then
Set adoParam = .CreateParameter("@Department", adVarChar,
adParamInput, Len(Department), Department)
.Parameters.Append adoParam
End If
If Len(Division) > 0 Then
Set adoParam = .CreateParameter("@Division", adVarChar,
adParamInput, Len(Division), Division)
.Parameters.Append adoParam
End If
If Len(Speciality) > 0 Then
Set adoParam = .CreateParameter("@Speciality", adVarChar,
adParamInput, Len(Speciality), Speciality)
.Parameters.Append adoParam
End If



'specify a stored prcoedure
.CommandType = adCmdStoredProc
'Brackets must surround stored procedure names with spaces
.CommandText = "sp_Create_ScoreCard_Report_Data"

'execute the the stored procedure
.Execute


'cleanup the ADO objects
Set adoParam = Nothing
Set adoRS = Nothing
Set adoCmd = Nothing


End With

Exit Sub

Error_Create_ScoreCard_Report_Data:
Debug.Print Err.Description
'cleanup the ADO objects
Set adoParam = Nothing
Set adoRS = Nothing
Set adoCmd = Nothing

End Sub

For example, if I run the store procedure from SQL Mgmt console specifying 1
optional parameter:
EXEC @return_value = [dbo].[sp_Create_ScoreCard_Report_Data]
@FiscalYear = N'2009',
@UserName = N'userid',
@Department = '022'

Data is inserted into the table as expected.

But when I try to call the VBA method above using the same parameter values,
no data is created. I can only surmise that the issue is that the VBA needs
to have something for the null/empty parameters.

The stored procedure is defined with default value of NULL for the optional
parameters.

Any help would be appreciated.
 
Hi Dave

I don't specifically know the answer to your question. However, I have done
something similar using VB. As VB and VBA are so similar its possible that my
code might work if you 'VBA' the syntax....

Dim cmd As New SqlClient.SqlCommand
With cmd
.connection = cnn ' cnn is the connection object
.CommandText = "SQL_proc_ClientAmend"
.CommandType = CommandType.StoredProcedure

.Parameters.AddWithValue("@Result", 0)
.Parameters("@Result").Direction =
ParameterDirection.InputOutput
.Parameters.AddWithValue("@Consultant", Me.consultant)
.Parameters.AddWithValue("@Status", Me.Status.Text)
.Parameters.AddWithValue("@GroupName", Me.Group.Text)
.etc etc.
.ExecuteNonQuery()


I seem to recall that it was essential for the Parameters to be added in
exactly the same order as they appear in the procedure.

As I say, I have no idea if this will help but it could point you in the
right direction.

Good luck!

BW



Dave said:
I am trying to call a stored procedure from Access, but I am having problems
with optional parameters. If I execute the stored procedure from the SQL Mgmt
console it executes fine and inserts data into the table. When I try the same
thing from my VBA code no data is inserted. Here is the VBA code:

Public Sub Create_ScoreCard_Report_Data(ByVal FiscalYear As String, _
ByVal UserName As String, _
Optional ByVal PhysicianId As String =
"", _
Optional ByVal Department As String =
"", _
Optional ByVal Division As String = "", _
Optional ByVal Speciality As String = "")
'@FiscalYear AS NVARCHAR(4),
'@UserName as nvarchar(20),
'@PhysicianId AS NVARCHAR(255)= NULL,
'@Department AS NVARCHAR(255)= NULL,
'@Division AS NVARCHAR(10) = NULL,
'@Speciality AS NVARCHAR(255) = NULL --,

On Error GoTo Error_Create_ScoreCard_Report_Data

Set adoCmd = New ADODB.Command
Set adoParam = New ADODB.Parameter
Set adoRS = New ADODB.Recordset


With adoCmd
'set the connection last
Set .ActiveConnection = CurrentProject.Connection
'create the return parameter
'Set adoParam = .CreateParameter("@RecordsCreated", adInteger,
adParamReturnValue)
'.Parameters.Append adoParam
'create and append the input parameters
Set adoParam = .CreateParameter("@FiscalYear", adVarChar,
adParamInput, Len(FiscalYear), FiscalYear)
.Parameters.Append adoParam
Set adoParam = .CreateParameter("@UserName", adVarChar,
adParamInput, Len(UserName), UserName)
.Parameters.Append adoParam
If Len(PhysicianId) > 0 Then
Set adoParam = .CreateParameter("@PhysicianId", adVarChar,
adParamInput, Len(PhysicianId), PhysicianId)
.Parameters.Append adoParam
End If
If Len(Department) > 0 Then
Set adoParam = .CreateParameter("@Department", adVarChar,
adParamInput, Len(Department), Department)
.Parameters.Append adoParam
End If
If Len(Division) > 0 Then
Set adoParam = .CreateParameter("@Division", adVarChar,
adParamInput, Len(Division), Division)
.Parameters.Append adoParam
End If
If Len(Speciality) > 0 Then
Set adoParam = .CreateParameter("@Speciality", adVarChar,
adParamInput, Len(Speciality), Speciality)
.Parameters.Append adoParam
End If



'specify a stored prcoedure
.CommandType = adCmdStoredProc
'Brackets must surround stored procedure names with spaces
.CommandText = "sp_Create_ScoreCard_Report_Data"

'execute the the stored procedure
.Execute


'cleanup the ADO objects
Set adoParam = Nothing
Set adoRS = Nothing
Set adoCmd = Nothing


End With

Exit Sub

Error_Create_ScoreCard_Report_Data:
Debug.Print Err.Description
'cleanup the ADO objects
Set adoParam = Nothing
Set adoRS = Nothing
Set adoCmd = Nothing

End Sub

For example, if I run the store procedure from SQL Mgmt console specifying 1
optional parameter:
EXEC @return_value = [dbo].[sp_Create_ScoreCard_Report_Data]
@FiscalYear = N'2009',
@UserName = N'userid',
@Department = '022'

Data is inserted into the table as expected.

But when I try to call the VBA method above using the same parameter values,
no data is created. I can only surmise that the issue is that the VBA needs
to have something for the null/empty parameters.

The stored procedure is defined with default value of NULL for the optional
parameters.

Any help would be appreciated.
Click to expand...
 
Seems like you are reusing your parameter adoParam over and over again. You
will have to declare several ado parameters, one for fiscalyear, another for
username, another for PhysicianId etc

You put one value into adoParam and overwrite in your next lines of code.
When you actually call your stored procedure it seems like this parameter
will only hold the last value you allocated.

I am a bit surprised that your code run at all. When you try to append
adoParam several times, you should get an error on the second attempt. I
think you get the error and your code jumps to your errorhandler at the end.
This may explain why you dont get the response you expect .To test this
remove your errorhandler. Or you can open your vba editor. In the menu bar
select Tools->Options->General->Error Trapping. Set error trapping to "Break
on all errors". Run your code again and you can see where the error occurs.

I am not very much used to optional parameters. I would check the value of
each optional parameter in my vba procedure to be sure what kind of null
value I pass to the stored procedure on sql server.

Regards

Tore
 
Back
Top