External Data from Stored Procedure with parameters

[Ben Rum]

How do I source external data from a SQL Server stored Procedure with
parameters..

A stored procedure *without* parameters is not problem --> (get external
data - new db query - enter "exec sp_MySpName" into MS-Query)


However, with adding parameters, when the user selects "Refresh Data!" I
would like the user to be prompted to enter the values as per the stored
procedure parameters..

e.g. exec sp_MySpName @StartDate = [prompt user], @EndDate = [prompt user]

Is this possible?

 

[Jean-Yves]

Hi Ben,

Yes it is possible.
Turn the macro recorder on before you make a query. This should give you
some code.
Below is a copy of how I execute a query on a Oracle DB. The function
StringToArray is from Microsoft as there is a problem with the length of
the SQL string .
Regards,
Jean-Yves

Option Explicit
Sub GetStudentdata()
Dim strWhat As String
Dim StrFrom As String
Dim strWhere As String
Dim strOrder As String
Dim strSql As String
Dim varSql As Variant
strWhat = "SELECT SURNAME, NICKNAME,ID_PAYROLL,OPS_SECTOR"
StrFrom = "FROM PDMS.PDMS_STAFF_MEMBERS"
strWhere = "WHERE
nvl(OPS_RECOURSE_TYPE||OPS_RECOURSE_NUM,OPS_INIT_COURSE_TYPE||OPS_INIT_COURS
E_NUM) = '" & Range("H3").Text & Range("H6").Value & " ' "
strOrder = "ORDER BY SURNAME"
strSql = strWhat & " " & StrFrom & " " & strWhere & " " & strOrder
varSql = StringToArray(strSql)
Range("A2").Select
With Range("A2").QueryTable
.Connection = _
"OLEDB;Provider=MSDAORA.1;Password=password;User ID=username;Data
Source= DatabaseName"
.CommandType = xlCmdSql
.CommandText = varSql
.Refresh BackgroundQuery:=False
End With

End Sub


Function StringToArray(Query As String) As Variant

Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String
Dim i
' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.

StringToArray = Temp

End Function

 

[Jamie Collins]

when the user selects "Refresh Data!" I
would like the user to be prompted to enter the values as per the stored
procedure parameters..

e.g. exec sp_MySpName @StartDate = [prompt user], @EndDate = [prompt user]

Is this possible?

It is not possible to get MSQuery to automatically prompt for the
parameters.

To be able to use MSQuery parameters, the SQL must be a simple SELECT
written in MSQuery's own SQL dialect, rather than that odbc syntax or
the dialect of the database server. Using odbc's CALL syntax, ANSI's
EXECUTE syntax or anything proprietary will result in the 'cannot
display graphically' (whatever that means) message, after which
built-in support for parameters is lost.

You can, of course, write VBA to dynamically change the querytable's
SQL
text, then everything will be up for grabs, including throwing up
userforms with calendar controls on them, but you will have to write it
yourself <g>.

Jamie.

--