Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
External Data from Stored Procedure with parameters
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
External Data from Stored Procedure with parameters
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_C OURSE_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;Us er 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 "Ben Rum" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
External Data from Stored Procedure with parameters
Ben Rum wrote: 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data return from SQL Stored procedure... | Excel Discussion (Misc queries) | |||
Extract data from Stored procedure | Excel Discussion (Misc queries) | |||
Stored Procedure call and passing parameters | Excel Programming | |||
Importing Data from a Stored Procedure (SQL SErver) | Excel Programming | |||
How can I grab data from a SQL Server stored procedure | Excel Programming |