Passing a variable in a stored procedure

hearthhrob4gals

Board Regular
Joined
Aug 20, 2014
Messages
142
Hi,

I'm firing a sql stored procedure from excel. Just struggling to pass the variables (dates). any help on this would be appreciated.
VBA Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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.

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

hearthhrob4gals

Board Regular
Joined
Aug 20, 2014
Messages
142
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.

VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Is that the exact code?

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

hearthhrob4gals

Board Regular
Joined
Aug 20, 2014
Messages
142

ADVERTISEMENT

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...

VBA Code:
•    [dbo].[usp_GetSummaryReport_SFS_Combined_ByField] @choice,@value
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
strSQL = "[dbo].[usp_GetSummaryReport_SFS_Combined_ByField] " & Variable1 & ", " & Variable2
Set rs = conn.Execute(strSQL)
 

hearthhrob4gals

Board Regular
Joined
Aug 20, 2014
Messages
142
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.
VBA Code:
strSQL = "[dbo].[usp_GetSummaryReport_SFS_Combined_ByField] " & Variable1 & ", " & Variable2
Set rs = conn.Execute(strSQL)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
That's normal, if you want to get the 'headers' you'll need to loop through the Fields collection of the recordset.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,666
Messages
5,637,662
Members
416,979
Latest member
juliegeorge792

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top