Calling stored procedure with variables in SQL SERVER from Excel

[mrfitness79]

Hi there, I am trying to call a procedure in SQL SERVER from Excel and having some difficulty. In the past I have been able to connect to SQL SERVER to queries tables and return data, but this is different as its passing variables to SQL SERVER procedure.

This procedure I want to call imports data from a CSV file and runs some validation based on variables passed to it. The procedure is as the following (red are the sample values required for it to run):
[dbo].[TEMP_RU_PROFILER_MAIN]
@sProfilerPath = ' X:\RM\RMT\Profiler\SQL_Files\P00001_01.csv ',
@iSID = 1,
@iPID = 2,
@Frequency=3,
@Time_Period='month',
@Batch_Type='append'

Rules:

@sProfilerPath - path of the csv file saved (we have to pass the complete path for each batch)
@iSID- Session Id – unique for each time user opens the tool
@iPID – Batch Id – defaults at 1, increments each time user runs code in a session (each batch will have its own CSV file)
@Frequency – frequency (Number of months or weeks or days -if user entered 6 months, this would be ‘6’)
@Time_Period – Time Period(‘month’ or ‘week’ or ‘day’)
@Batch_Type – Batch type (‘Append’ or ‘Delete’ )

The function below I have written, indicating where I need help , and right after I show code which queries the table after procedure is completed

sub run_it_sample()
Dim cnSQL As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL as string
 
Set cnSQL = fnConnectSQLServer(CONST_SQL_SERVER_ADDRESS) 'centralized function I have to connect, the variable is the IP address
    
Dim strPath as string, strSession as string
Dim intBatch as integer, intFreq as integer
Dim strTimePd as string, strBatchType as string

'--------------------------------------------------------------------------------------------------
[COLOR="#FF0000"][B]'need to call procedure here to run validation in SQL SERVER [/B][/COLOR]



'--------------------------------------------------------------------------------------------------

'after procedure we attain tempships to show user

    strSQL = "SELECT sum(round(ship*annualized,0)) AS tot_ships From temp_ru_profiler_final" 
    strSQL = strSQL & " WHERE session_id = '1'" 
    strSQL = strSQL & " AND"
    strSQL = strSQL & " error_message is null"
    
    Set rs = cnSQL.Execute(strSQL)
    
    If rs.EOF Then
        tempships = rs!tot_ships
        msgbox tempships
    End If

    Set rs = Nothing
    Set cnSQL = Nothing
end sub