Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pass paramter to SQL server stored procedure
I have a SQL server procedure like following:
MystoredProc @DateLow as DateTime, @DateHigh as DateTime I would like to use Excel to retrieve information. I wanted to use MS SQL and pass cell A1 as DateLow and A2 as DateHigh. Is it possible to do this? If yes, Can you please give me some instructions to do it? Thanks millions, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pass paramter to SQL server stored procedure
Hello Souris,
Here is some VBA code that shows one way to run a parameterized SQL stored procedure and return and display the results on an Excel spreadsheet. Sub OpencnnSQL() Dim cnnSQL As ADODB.Connection Dim cmdSQL As ADODB.Command Dim rstSQL As ADODB.Recordset Dim prm(2) As ADODB.Parameter Dim strCnn As String, strSQL As String Dim iCol As Integer, fldCount As Integer strCnn = Empty strCnn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" & _ "Initial Catalog=Northwind;Data Source=localhost" strSQL = Empty ' This stored procedure is in the SQL Server 2000 Northwind dB. ' It takes 2 parameters (@Beginning_Date and @Ending_Date) and returns 6 fields. strSQL = strSQL & "dbo.[Employee Sales by Country]" Set cnnSQL = New ADODB.Connection Set cmdSQL = New ADODB.Command Application.StatusBar = "Connecting ..." With cnnSQL .CursorLocation = adUseClient .Open strCnn End With With cmdSQL .ActiveConnection = cnnSQL .CommandText = strSQL .CommandType = adCmdStoredProc Set prm(1) = .CreateParameter("Beginning_Date", adDate, adParamInput) ' Parameter values could also be passed to this subroutine or retrieved from user at run-time. prm(1).Value = "June 1, 1997" Set prm(2) = .CreateParameter("Ending_Date", adDate, adParamInput) prm(2).Value = "June 30, 1997" For i = 1 To UBound(prm) .Parameters.Append prm(i) Next i End With Application.StatusBar = "Executing ..." Set rstSQL = cmdSQL.Execute ' Select destination for results With ThisWorkbook.Worksheets(6) .Activate .UsedRange.EntireColumn.Delete Application.StatusBar = "Populating ..." fldCount = rstSQL.Fields.Count For iCol = 1 To fldCount .Cells(1, iCol).Value = rstSQL.Fields(iCol - 1).Name Next .Cells(2, 1).CopyFromRecordset rstSQL Application.StatusBar = "Formatting ..." .Rows(1).Font.Bold = True .UsedRange.Columns.AutoFit .Cells(1, 1).Activate End With Application.StatusBar = "Closing ..." rstSQL.Close cnnSQL.Close Set rstSQL = Nothing Set cmdSQL = Nothing Set cnnSQL = Nothing Application.StatusBar = False End Sub Is that what you were looking for? HTH and let me know how you get on. Cheers, Sean. "Souris" wrote: I have a SQL server procedure like following: MystoredProc @DateLow as DateTime, @DateHigh as DateTime I would like to use Excel to retrieve information. I wanted to use MS SQL and pass cell A1 as DateLow and A2 as DateHigh. Is it possible to do this? If yes, Can you please give me some instructions to do it? Thanks millions, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Query to read data from SQL server stored procedure | Excel Programming | |||
How to pass an Excel range as an argument to a SQL Server stored Procedure | Excel Programming | |||
calling a stored procedure on MS SQL Server within MS Excel 2000 | 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 |