But I have query which pivotsĭata in dynamic way, so contains also parameters. Wednesday, Novem2:01:12 PM - Greg Robidoux
Is it becouse of variable number of columns? But when I try to use exactly the same method to get data from that procedure to excel It fails. So number of columns will be different each time depending on parameter. I have my stored procedure with dynamic pivot table.
Is it possible to use this solution when columns will be variable as well? However my IDname column has many rows, how can I run a query for every row (row after row) and get a result for all of them in same table. My only paramter is IDname (I do not have start and end dates) and it works as it should. Open a new Excel spreadsheet and enter our input parameters SellStartDate and SellEndDate as Labels inĬells A3 and A4 and make B3 and B4 as their input fields, format them as ON PS.ProductCategoryID=PC.ProductCategoryID SET NOCOUNT ON added to prevent extra result sets from To make our work easier, I created a stored procedure using the below query with SellStartDate and SellEndDate as parameters.ĬREATE PROCEDURE dbo.ProductListPrice as Date, as Date The click of the command button will be coded in Visual Basic to pass the values fromįor our illustration, I have used a query from the AdventureWorks2014 database to find the List Price of all products by their SellStartDate and SellEndDate. We have to create the necessary Data Connection to Connect to SQL Server, we will be passing the values entered in the cells to the Query by theĬlick of an ActiveXControl Command Button. In this tip we will learn about passing parameters from ExcelĬells to a Query at run time to import the data from We have always used Excel as a presentation layer to import data from SQL Server for analysis.įor queries using parameters - if we have to change the values of parameters, we have to go to the Connection and make the change which isn'tĪn easy way of doing it. By: Junaith Haja | Updated: | Comments (60) | Related: More > Microsoft Excel Integration