0

I'm trying to use a parameter taken from an Excel spreadsheet multiple times in an SQL query, and I don't find any easy solutions, while I think it shouldn't be that complicated. I work on SQL server.

Let me explain. I have a table named DONNEES_CNA which has as columns a start time (STARTIME), an end time (ENDTIME) and a tonnage (TONNAGE). It gives the tonnage (in tons) that went onto a conveyor between start time and end time.

Let's say I have data every hour. Start times will be like '2016-03-01 10:00:00' and end times let's say '2016-03-01 10:59:59'.

Thus, if I want the tonnage between two dates/hours, my SQL query would be like :

DECLARE @DEBUT DATETIME, @FIN DATETIME
SET @DEBUT = '2016-03-01 09:00:00'
SET @FIN = '2016-03-01 12:30:00'

SELECT  CASE    WHEN STARTTIME >= @DEBUT AND ENDTIME <= @FIN THEN TONNAGE
        WHEN STARTTIME >= @DEBUT AND ENDTIME > @FIN THEN (TONNAGE)*DATEDIFF(SECOND, STARTTIME, @FIN)/DATEDIFF(SECOND, STARTTIME, ENDTIME)
        WHEN STARTTIME < @DEBUT AND ENDTIME <= @FIN THEN (TONNAGE)*DATEDIFF(SECOND, @DEBUT, ENDTIME)/DATEDIFF(SECOND, STARTTIME, ENDTIME)
        WHEN STARTTIME < @DEBUT AND ENDTIME > @FIN THEN (TONNAGE)*DATEDIFF(SECOND, @DEBUT, @FIN)/DATEDIFF(SECOND, STARTTIME, ENDTIME)
    END 
AS TONNAGE
FROM DONNEES_CNA

It might not be optimized at all, is extracted from a way bigger query and is just an example amongst many I have ; the idea is just that I use many times only two parameters.

It works fine on SQL Server, on SSRS, but I can't do it on Excel. I just want to have @DEBUT and @FIN directly from my spreadsheet.

I tried SET @DEBUT = ?, idem for @FIN which doesn't work. I put on the top of my query SET NOCOUNT ON, doesn't work neither. The first error is something like (sorry my Excel is in French) : "Parameter number is not valid".

I followed answer to this which doesn't work neither (or I do it wrong ?) : How to add parameters to an external data query in Excel which can't be displayed graphically?

I found this, but it would be too long to deal with every parameter : how to pass parameters to query in SQL (Excel)

Is the only solution to put ? everytime I have a parameter and define each parameter in Excel ? My query can be quite long, and these parameters may appear some hundred times ...

I am not reluctant to use VBA, as long as there is a solution which doesn't make me click a hundred time on the same A2 and B2 cells where my parameter would be ..

If there already is an answer to this case, please excuse me, I've been searching for a long time without success.

Thanks for your help !

Community
  • 1
  • 1
BD01
  • 116
  • 7

1 Answers1

0

I just read about stored procedure, I didn't think about it, and this could in fact be a real solution, and do something like exec procedure_CNA ?, ?.

This just mean passing every query in stored procedure. If there is an only Excel solution, I am more interested !

BD01
  • 116
  • 7