Return results from SQL Server stored procedure using variables in ADODB

Question

I'm working on a rec process which will pull data out of a SQL Server database into Excel and out of another application to compare the two sources.

I've written some code that will pull the data into a ADODB record set and drop it into an Excel sheet from a SQL Server view. This works as expected.

I need the query to be more dynamic and as it is quite resource intensive in the real query. I think it would be better to use a stored procedure, passing parameters to the query and only returning the result sets I need.

I don't want the SQL query to be complicated within the VBA though which is why I can't just define the query there.

The following code returns no records to the record set and exits.

Running the stored procedure in Management Studio using the same parameter gives the results expected in SSMS.

Stored procedure:

CREATE PROCEDURE [dbo].[JoshTest]  
    @AccCode nvarchar(50)     
AS   
    SET NOCOUNT ON;  

    SELECT 
        [Account_SKey], [Hierarchy_SKey], [Account_Code], [Leaf]
    FROM 
        [dbo].[VW_DIM_ACCOUNT_LEAF]
    WHERE 
        [Leaf] = @AccCode;
GO

VBA in Excel:

Sub test_stored_proc()

Dim AccountCodeVar As String
AccountCodeVar = Range("ACCCODESEL").Value
Dim osheet As String 'output sheet
Dim orange As String 'output range
osheet = "Output1"
orange = "A3"
Call ConnectSqlServerStoredProc(osheet, orange, AccountCodeVar)
End Sub


Function ConnectSqlServerStoredProc(osheet As String, orange As String, AccountCodeVar As String)

'Clear previous results
Sheets("Output1").Range("A3:D60000").Clear

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=CSIMCCS01; Initial Catalog=T_EXP_CPM; Integrated Security=SSPI;"

Debug.Print sConnString
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection and execute.
conn.Open sConnString

Set cmd = New ADODB.Command
cmd.CommandText = "JoshTest"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn

'set parameter values

Set prm = cmd.CreateParameter(Name:="AccCode", Type:=adVarChar, Direction:=adParamInput, Size:=10)
cmd.Parameters.Append prm
cmd.Parameters("AccCode").Value = "'" & AccountCodeVar & "'"

Debug.Print cmd.Parameters("AccCode").Value

Set rs = cmd.Execute

' Check we have data.
If Not rs.EOF Then
    ' Transfer result.
Sheets(osheet).Range(orange).CopyFromRecordset rs
' Close the recordset
    rs.Close
Else
    MsgBox "Error: No records returned.", vbCritical
End If

' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

MsgBox ("Done")

End Function

I looked at several examples of similar questions but they all try to return a single value, such as status / user id etc. rather than a data set.

Answer 1

Worked it out eventually... the issues was that I was putting single quotes either side of the parameter value. I thought that this would be correct as SQL would require them but taking them out of the VBA has resolved the issue.

@Mister 832 - thanks for your help in stepping this through.

In the end the solution was changing:

cmd.Parameters("@AccCode").Value = "'" & AccountCodeVar & "'"

to

cmd.Parameters("@AccCode").Value = AccountCodeVar

Regards

Answer 2

Have you tried adding an @ to the parametername?

Set prm = cmd.CreateParameter(Name:="@AccCode", Type:=adVarChar, Direction:=adParamInput, Size:=10)
cmd.Parameters.Append prm
cmd.Parameters("@AccCode").Value = "'" & AccountCodeVar & "'"

I got it working on my pc.

Remove the line Set rs = New ADODB.Recordset and change the recordsetline to Set rs = cmd.Execute