locked
How to Pass input Parameters from Excel which is used in Excel services to the Stored procedure

    Question

  • hi,
      I am connecting the Excel with the Sql server 2005 using Data-> connections in the Excel work book which is published in the Excel web access web part.
    I am calling one stored procedure to display the values.
    How to pass parameters to the Stored procedure from Excel ??
    As Excel services doesn't support external query tables which can be created using Microsoft Excel Query window.
    Any pointers Regrading this will be so much helpful . Thanks in advance

    mamu
    Friday, August 01, 2008 9:24 AM

Answers

  • To answer the OP, there is no native support for parametrized queries in Excel Services.  You would need custom code such as a UDF to do the query for you.

    To answer the other question of how to do it in Excel (not Excel Services), you can use the other solution mentioned here (write some VBA that will modify the query) or you can use Data > From Other Sources > From Microsoft Query, and construct your query there.  Click the Help icon, and then search for "Create a parameter query".  Parameters will be returned to Excel as question marks in the SQL.  Then the Parameters button will be active, and you can configure those parameters and where their input values should come from, including a cell in the workbook.  This only works with ODBC data sources.

    Tuesday, June 08, 2010 6:15 PM

All replies

  • Hi All,
    I have the same question i.e. How do I pass to a Stored Procedure from Excel 2007. The Spreadsheet uses a Pivot Table, the data for which is retrieved using this SP. 


    On the definition Tab of the Data Connection in Excel 2007, I do see button "Parameters" for that but for some reason it is disabled. What could be the reason for that?

    The command type on the same screen is "SQL" and Command Text contains the Stored Procedure called in the format:  DbName.Dbo.SpName

    The SP works ok when there are no parameters passed. Now I want to pass parameters to it.
    Further I want to take the parameters from content of a Cell. Example: User can type a Year in a Cell and that will be send as a Parrameter to the Stored Procedure, which I believe is possible as long as I can access the Parameters button above.


    Would appreciate your help.

    -- Mo


    • Edited by Mohan Taneja Monday, October 12, 2009 7:26 AM Added More Info
    Monday, October 12, 2009 7:13 AM
  • Hi Mohan,

    Did you get any help on your request?
    Friday, December 18, 2009 11:17 AM
  • Dim con As Connection
    Dim rst As Recordset

    Set con = New Connection
    con.Open "Provider=SQLOLEDB;Data Source=LAPTOP\SQL_EXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;"

    Set rst = con.Execute("Exec dbo.TestNewProc '" & _
    ActiveSheet.Range("E1").Text & "'")

    ActiveSheet.Range("A5").CopyFromRecordset rst

    rst.Close
    con.Close

     

    Non-code method (Excel 2007):

    Data > From Other Sources > From SQL Server > ... Name ... > ... Select DB ... > Select table ... > Finish > Yes > Properties > Definition > Command Type = SQL > Commant Text = exec dbo.TestNewProc 'usa'

    Good luck!!

    • Proposed as answer by ryguy72 Thursday, May 27, 2010 4:47 AM
    • Unproposed as answer by Mike Walsh FIN Thursday, June 03, 2010 3:20 AM
    • Proposed as answer by ryguy72 Tuesday, June 08, 2010 2:29 PM
    • Unproposed as answer by Mike Walsh FIN Tuesday, June 08, 2010 3:57 PM
    Wednesday, May 26, 2010 3:19 PM
  • ryguy72:

     

    Please do not propose your own posts as answers. It is not helpful.

     

    Propose the good answers of other people. Wait for someone else to propose your posts.

     

    (Moderator)


    2010 Books: SPF 2010; SPS 2010; SPD 2010; InfoPath 2010; Workflow etc.
    2007 Books: WSS 3.0; MOSS 2007; SPD 2007; InfoPath 2007; PerformancePoint; SSRS; Workflow
    Both lists also include books in French; German; Spanish with even more languages in the 2007 list.
    Thursday, June 03, 2010 3:21 AM
  • Ok, thanks for the heads up Mike.  I got help from someone on LinkedIn and posted here to share with others, a solution which worked for me.  That's all...

     

    Tuesday, June 08, 2010 2:29 PM
  • Now you've read my message, wait until *someone else* ,proposes your above post. Then (If) and only then will I mark it up to answer status.

    We still need that confirmation from either the OP or a "neutral".

     


    2010 Books: SPF 2010; SPS 2010; SPD 2010; InfoPath 2010; Workflow etc.
    2007 Books: WSS 3.0; MOSS 2007; SPD 2007; InfoPath 2007; PerformancePoint; SSRS; Workflow
    Both lists also include books in French; German; Spanish with even more languages in the 2007 list.

    Tuesday, June 08, 2010 3:59 PM
  • To answer the OP, there is no native support for parametrized queries in Excel Services.  You would need custom code such as a UDF to do the query for you.

    To answer the other question of how to do it in Excel (not Excel Services), you can use the other solution mentioned here (write some VBA that will modify the query) or you can use Data > From Other Sources > From Microsoft Query, and construct your query there.  Click the Help icon, and then search for "Create a parameter query".  Parameters will be returned to Excel as question marks in the SQL.  Then the Parameters button will be active, and you can configure those parameters and where their input values should come from, including a cell in the workbook.  This only works with ODBC data sources.

    Tuesday, June 08, 2010 6:15 PM
  • The MS Query option does not work with complex queries (e.g. the ones with CTEs). If the query only retrieves data from a single table, then the workaround appears to be working.

    Thursday, August 11, 2011 4:15 PM