Passing a variable in a stored procedure

[hearthhrob4gals]

Hi,

I'm firing a sql stored procedure from excel. Just struggling to pass the variables (dates). any help on this would be appreciated.

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=rpasql01;" & _
                  "Initial Catalog=RPA_PROD;" & _
                  "Integrated Security=SSPI;"
    
    ' 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 rs = conn.Execute("[dbo].[usp_GetSummaryReport_SFS_Combined_ByDate] '2021-01-28','2021-01-29'")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets("Output").Range("A1").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
    
End Sub

Regards,
Yousuf

 

[Norie]

All you should need to do is incorporate the dates, in the correct format, in the SQL statement.

For example, let's say you have 2 date variables - Date1 and Date2.

Set rs = conn.Execute("[dbo].[usp_GetSummaryReport_SFS_Combined_ByDate] '" & Format(Date1, "yyyy-mm-dd") & "','" & Format(Date2, "yyyy-mm-dd") & "'")

 

[hearthhrob4gals]

All you should need to do is incorporate the dates, in the correct format, in the SQL statement.

For example, let's say you have 2 date variables - Date1 and Date2.

Set rs = conn.Execute("[dbo].[usp_GetSummaryReport_SFS_Combined_ByDate] '" & Format(Date1, "yyyy-mm-dd") & "','" & Format(Date2, "yyyy-mm-dd") & "'")

Thanks....It worked perfect.....Need one more small help ...how to add variable in the below line.. @choice should be replaced by variable1 and instead of @VALUE should be replaced by variable 2

Set rs = conn.Execute("[dbo].[usp_GetSummaryReport_SFS_Combined_ByField]" @choice,@VALUE)

 

[Norie]

Is that the exact code?

It doesn't look right with @choice and @VALUE outside the quotes.

 

[hearthhrob4gals]

Is that the exact code?

It doesn't look right with @choice and @VALUE outside the quotes.

exactly....how this can be corrected ....this is teh stored procedure taht i was provided...

•    [dbo].[usp_GetSummaryReport_SFS_Combined_ByField] @choice,@value

 

[Norie]

Try this.

strSQL = "[dbo].[usp_GetSummaryReport_SFS_Combined_ByField] " & Variable1 & ", " & Variable2
Set rs = conn.Execute(strSQL)

 

[hearthhrob4gals]

superb....it worked...one final question...my code is giving my data without the header....is that the general behavior or there is something missing in my code?

anyways thanks a ton.....: )

Try this.

strSQL = "[dbo].[usp_GetSummaryReport_SFS_Combined_ByField] " & Variable1 & ", " & Variable2
Set rs = conn.Execute(strSQL)

 

[Norie]

That's normal, if you want to get the 'headers' you'll need to loop through the Fields collection of the recordset.