Running MSSQL with parameters from Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Running MSSQL with parameters from Excel

Author

Topic

Scubades
Starting Member

4 Posts

Posted - 2011-04-20 : 04:32:26

I have a spreadsheet with a min & max values in two cells. I currently have MSSQL code that extracts the data for all values.

What I want to be able to do is to pass the max and min values as parameters to the SQL so that it only extracts that reduced range back to the spreadsheet.

I am a competent Excel user, but fairly new to MS SQL

Scubades
Starting Member

4 Posts

Posted - 2011-04-28 : 03:44:54

Have I posted this in the wrong part of the forum? Although 46 people have read my post, no one has offered a possible answer to what I had hoped would be a regular type of query

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-04-28 : 04:18:32

There's probably a better way to do what you're doing not using excel, but if you must you're either going to have to create a stored procedure in the database that requires the parameters you mention and call it from vba referencing the cells containing the paramaters, or write parameterised SQL in vba, again referencing the cells with the parameters in.

I don't think there's an easy way to reference parameters without vba like there is in lots of reporting software.

Scubades
Starting Member

4 Posts

Posted - 2011-04-28 : 05:06:04

Thanks for the reply Michael, I can only wait and hope that someone out there has achieved what I am looking for. Unfortuinately I am considerably more experienced with Excel than I am with MSSQL. I can achieve what I want by manually manipulating my SQL and submitting from within Excel, so in a sense I have a workaround.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-04-28 : 05:19:23

Vba is something in excel, which I would expect you to be a little familar with if you're experienced in excel as it's the language generated when recording macros and can also be manually edited. To use it with SQL you'll have to connect to the database with in vba, but that's pretty simple if you look it up. If you don't know any vba then I think you'll have to stick to the method you have already unless you have the time to learn some.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-28 : 05:58:03

Here's a snippet from an old Excel sheet where I connected to a SQL Server and passed parameters to a stored procedure (relevant portions in red):

Sub GetAccountTemplate(Account$)
Dim rs As New ADODB.Recordset, cn As New ADODB.Connection, wrk As Worksheet, conn$, rw As Range
conn = "Provider=SQLOLEDB;Data Source=MyServer;User ID=MyUser;Password=MyPassword;Initial Catalog=MyDB;"
Set wrk = Worksheets("Form")
ClearData                                                           ' clear any existing data on spreadsheet
wrk.Unprotect                                                       ' unprotect worksheet for editing
Set rw = wrk.Range("First").Cells(1, 1)                             ' set worksheet range to object variable
cn.Open conn
rs.Open "exec MyDB.dbo.ProcedureName '" & Account & "' ", cn        ' run procedure to retrieve bill template data for account
If rs.EOF Then GoTo theend                                          ' if no data skip
wrk.Range("Account").Value = rs("Account")                          ' set account cell
wrk.Range("Vendor").Value = rs("Vendor")                            ' set vendor cell
wrk.Range("CostCenter").Value = rs("CostCenter")                    ' set cost center cell
Do While Not rs.EOF                                                 ' enumerate rows
    rw.Cells(1, BillCols.Commodity) = rs("Commodity")               ' set commodity
    rw.Cells(1, BillCols.Meter).Value = rs("Meter")                 ' set meter
    rw.Cells(1, BillCols.Caption).Value = rs("Caption")             ' set charge description/caption
    rw.Cells(1, BillCols.Units).Value = rs("Units")                 ' set unit of measure
    rw.Cells(1, BillCols.Amount).Locked = False                     ' unlock amount cells for editing
    rw.Cells(1, BillCols.DisplayOrder).Value = rs("Order")          ' set display order in hidden column
    If rs("Units") = "$" Then                                       ' if unit of measure is dollars
        rw.Cells(1, BillCols.Consumption).Value = "X"               ' lock use/demand (consumption) column
    Else
        rw.Cells(1, BillCols.Consumption).Locked = False            ' unlock use/demand (consumption) column
    End If
    Set rw = rw.Offset(1, 0)                                        ' move to next row on spreadsheet
    rs.MoveNext                                                     ' move to next row in recordset
Loop
rs.Close                                                            ' close recordset
cn.Close                                                            ' close connection
wrk.Protect                                                         ' protect sheet, lock cells
wrk.Range("FromDate").Activate                                      ' select From Date cell for data entry
Set rs = Nothing
Set cn = Nothing
Set wrk = Nothing
theend:
End Sub

If you're posting a data set to a range of cells, use the CopyFromRecordset method, it's documented in the Excel VBA help. I used a loop here because of a specific Excel layout, CopyFromRecordset is a lot easier and faster if you're just dumping data into a spreadsheet.

Scubades
Starting Member

4 Posts

Posted - 2011-04-28 : 07:13:32

Yes Michael, I do use vba for repetetive tasks in Excel, just haven't explored the SQL angle yet.
RobVolk,
Thanks for your snippet, I will review and see if I can implement for my situation (always prefer to re use an existing wheel rather than try to reinvent it

)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-28 : 07:25:19

One thing I can't recommend enough: use a stored procedure, DO NOT embed SQL in your VBA code. It will get messy with the string concatenation and it opens up details of your database you may not want revealed.

This is especially true if you have to embed a login and password to connect, vs. using Windows authentication. It opens a big security hole that's nearly impossible to plug. If you must embed a login and password, create a new login with minimal permissions to run the stored procedure, nothing else.

I'd also suggest looking at the ADODB.Command object and .Parameters objects. This will make the code cleaner than creating a Recordset against a Connection object. You can find examples on any old classic ASP web site like 4GuysFromRolla.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources