sql server - define parameters from an Excel Cell for a CASE WHEN in SQL

Question

I want to be able to define my parameter SA.FiscalWeek in the below code from a cell in Excel

SUM(CASE WHEN SA.FISCALYEAR = 2017 
AND SA.FISCALWEEK = 19 THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'Week'

I will be using multiple Case When in my overall code all with individual parameters

I have found a way this is possible using a WHERE statement,

But was wondering if it is possible in a CASE WHEN statement ?

Answer 1

You can use vba to pass the query to the command text, Use a helper sheet with your query listed line by line

=" line 1 of query"

=" line 2 etc. "

=" line 3&E4&"rest of line etc. "

E4 would be a cell ref to make the query dynamic from Excel then concatenate these lines into one cell,

use vba similiar to the below, my range "SQLCOUNTRY" is the cell of concatenated query

 With ActiveWorkbook.Connections("EUUKSQL01 dashboard StockSalesAggregateWeek2") _
        .OLEDBConnection
    .CommandText = Sheets("CountryScript").Range("SQLCOUNTRY").Value
    End With

This way any part of the query can be driven off an existing excel Cell