Parametizing query for Excel

Question

UPDATE:

I've managed to get the following query to work with Excel:

SELECT me.id ,me.merchant_num ,me.merchant_nm,
CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END AS production_mode,
Max(CASE WHEN tt.bank_txt = 'IBA' THEN tt.transaction_dt END) AS last_IBA_transaction_dt,
convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), trans_live)), 9, 1)) AS is_live
FROM Data.dbo.merchant_t me
LEFT JOIN Data.dbo.transaction_t AS tt
  ON tt.merchant_id = me.id 
where tt.transaction_dt >= ?
and tt.transaction_dt <= ?
and tt.trans_status = ? 
GROUP BY me.id,me.merchant_num,me.merchant_nm, me.status

This query is so close. All I need to do is add and remove the lines as indicated in the query comments below:

SELECT me.id ,me.merchant_num ,me.merchant_nm,
    Count(CASE WHEN tt.transaction_dt >= ? 
               AND tt.transaction_dt <= ? 
               THEN tt.id end) AS num_transactions -- ADD THIS COUNT(CASE) STATEMENT
    CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END AS production_mode,
    Max(CASE WHEN tt.bank_txt = 'IBA' THEN tt.transaction_dt END) AS last_IBA_transaction_dt,
    convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), trans_live)), 9, 1)) AS is_live
    FROM Data.dbo.merchant_t me
    LEFT JOIN Data.dbo.transaction_t AS tt
      ON tt.merchant_id = me.id 
    where tt.transaction_dt >= ? -- REMOVE THIS LINE
    and tt.transaction_dt <= ? -- REMOVE THIS LINE
    and tt.trans_status = ? 
    GROUP BY me.id,me.merchant_num,me.merchant_nm, me.status

The query works with Excel if I leave the dates in the following line:

Count(CASE WHEN tt.transaction_dt >= '2020-04-01' AND tt.transaction_dt <= '2020-04-30' THEN tt.id end) AS num_transactions

instead of parameterizing them as

Count(CASE WHEN tt.transaction_dt >= ? AND tt.transaction_dt <= ? THEN tt.id end) AS num_transactions

If I parameterize them, I get the error:

"Syntax error or access violation"

Obviously, I need those dates parameterized somehow that works.

END UPDATE

I have a SQL server query as follows:

WITH CTE_Merchants AS
(
    SELECT
        me.id, me.merchant_num, me.merchant_nm,
        COUNT(tt.id) as num_transactions,
        CASE 
           WHEN me.status = 'A' THEN 'Yes' 
           ELSE 'No' 
        END AS production_mode
    FROM
        merchant_t me
    LEFT OUTER JOIN 
        transaction_t tt ON tt.merchant_id = me.id 
                         AND tt.transaction_dt BETWEEN '2020-04-01' AND '2020-04-30' -- [PARAMETIZE BOTH DATES]
    WHERE 
        me.status = 'T' -- [PARAMETIZE]
    GROUP BY 
        me.id, me.merchant_num, me.merchant_nm, me.status
)
SELECT
    CTE_Merchants.id,
    CTE_Merchants.merchant_num,
    CTE_Merchants.merchant_nm,
    CTE_Merchants.num_transactions,
    CTE_Merchants.production_mode,
    A1.is_live,
    A2.last_IBA_transaction_dt
FROM
    CTE_Merchants
OUTER APPLY
    (SELECT TOP 1 transaction_t.trans_live AS is_live
     FROM transaction_t
     WHERE transaction_t.merchant_id = CTE_Merchants.id
     ORDER BY transaction_dt DESC) AS A1
OUTER APPLY
    (SELECT TOP 1 transaction_t.transaction_dt AS last_IBA_transaction_dt
     FROM transaction_t
     WHERE transaction_t.merchant_id = CTE_Merchants.id
       AND transaction_t.bank_txt = 'IBA'
     ORDER BY transaction_dt DESC) AS A2;

I want to use the query in an Excel spreadsheet and I need to parametrize the variables indicated within the query comments.

The following is the Excel Data connection dialogue:

Problem is: when I try to parametrize the variables, e.g. change

AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'

to

AND tt.transaction_dt BETWEEN ? and ?

I get an error

Invalid parameter number

followed by

Invalid Descriptor Index

The same thing happens if I change the BETWEEN statement to

AND (tt.transaction_dt >= ? and  tt.transaction_dt <= ?)

Also, my "Parameters" button is greyed out.

I suspect its something to do with the query itself, maybe because the parameters are buried in subqueries. I don't know enough about it. Any suggestions on how to get this working, anyway that works. I've heard that you can create a view and query the view, but I'm at the limit of my knowledge of SQL and Excel at this point.

I have already tried this solution without success: SQL Sub-query parameters from Excel

EDIT

I just tried behonji's suggestions from the following question: How to add parameters to an external data query in Excel which can't be displayed graphically?

Here I successfully created a parametized query in Excel, as follows:

SELECT merchant_t.id
FROM XXX.dbo.merchant_t merchant_t
where start_dt = ?
and create_dt = ?
and status = ?

Then, I substituted that query with my actual query, which I parameterized as follows:

WITH
CTE_Merchants
AS
(
    SELECT distinct
        me.id, me.merchant_num, me.merchant_nm
        ,count(tt.id) as num_transactions
        ,CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END as production_mode
    FROM
        merchant_t me
        LEFT OUTER JOIN transaction_t tt
            ON  tt.merchant_id = me.id 
            AND (tt.transaction_dt >= ? and tt.transaction_dt <= ?)
    WHERE me.status = ?
    GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status
)
SELECT
    CTE_Merchants.id
    ,CTE_Merchants.merchant_num
    ,CTE_Merchants.merchant_nm
    ,CTE_Merchants.num_transactions
    ,CTE_Merchants.production_mode
    ,A1.is_live
    ,A2.last_IBA_transaction_dt
FROM
    CTE_Merchants
    OUTER APPLY
    (
        select top 1
            transaction_t.trans_live AS is_live
        from transaction_t
        where
            transaction_t.merchant_id = CTE_Merchants.id
        order by transaction_dt desc
    ) AS A1
    OUTER APPLY
    (
        select top 1
            transaction_t.transaction_dt AS last_IBA_transaction_dt
        from transaction_t
        where
            transaction_t.merchant_id = CTE_Merchants.id
            and transaction_t.bank_txt = 'IBA'
        order by transaction_dt desc
    ) AS A2
;

I get exactly the same errors as before.

have you followed these steps? https://stackoverflow.com/questions/3091908/how-to-add-parameters-to-an-external-data-query-in-excel-which-cant-be-displaye — Alfin E. R., Jun 15 '20 at 08:19
Excel doesn't allow parameters in CASE? Outch. Did you try to create the Select-statement in Excel and then simply run it as-is like this https://stackoverflow.com/a/33626769/2527905 or create a Stored Procedure — dnoeth, Jun 20 '20 at 09:45

score 2 · Accepted Answer · answered Jun 20 '20 at 05:22

I know nothing about Excel and how to run queries from Excel, but as a last resort you can try to wrap your complex query into a stored procedure or a table-valued function and call it from Excel. Something like below.

Besides, it is good to have a clearly defined interface to a DB (in a form of a procedure/function), even if you technically could put a complex query into the Excel spreadsheet directly. It will make it easier to maintain your code and configure permissions if needed. (You can give your Excel user rights to execute just this stored procedure and nothing else, so that they could not mess with the database.)

Stored procedure

CREATE PROCEDURE [dbo].[ReadMerchants]
    @ParamStartDate date,
    @ParamEndDate date,
    @ParamStatus nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    -- Your query using the stored procedure parameters
    SELECT
    ...
    ;

END

This is how you call it from Excel.

EXEC [dbo].[ReadMerchants]
    @ParamStartDate = ?,
    @ParamEndDate = ?,
    @ParamStatus = ?

Table-valued function

CREATE FUNCTION [dbo].[GetMerchants]
(
    @ParamStartDate date,
    @ParamEndDate date,
    @ParamStatus nvarchar(10)
)
RETURNS TABLE
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT
    ...

)

This is how you call it from Excel.

SELECT * FROM [dbo].[GetMerchants](?, ?, ?)

(Obviously, it is better to explicitly list all columns, rather than type *.)

If both stored procedure and table-valued function work with Excel, I'd personally use stored procedure - you can put any kind of complex logic into it if necessary. Functions are more limited.

score 0 · Answer 2 · answered Jun 18 '20 at 13:32

please try to edit your query like this

declare @dateFrom datetime = ?
declare @dateTo datetime = ?
declare @status varchar(20) = ?

WITH
CTE_Merchants
AS
(
    SELECT distinct
        me.id, me.merchant_num, me.merchant_nm
        ,count(tt.id) as num_transactions
        ,CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END as production_mode
    FROM
        merchant_t me
        LEFT OUTER JOIN transaction_t tt
            ON  tt.merchant_id = me.id 
            AND (tt.transaction_dt >= @dateFrom and tt.transaction_dt <= @dateTo)
    WHERE me.status = @status
    GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status
)
SELECT
    CTE_Merchants.id
    ,CTE_Merchants.merchant_num
    ,CTE_Merchants.merchant_nm
    ,CTE_Merchants.num_transactions
    ,CTE_Merchants.production_mode
    ,A1.is_live
    ,A2.last_IBA_transaction_dt
FROM
    CTE_Merchants
    OUTER APPLY
    (
        select top 1
            transaction_t.trans_live AS is_live
        from transaction_t
        where
            transaction_t.merchant_id = CTE_Merchants.id
        order by transaction_dt desc
    ) AS A1
    OUTER APPLY
    (
        select top 1
            transaction_t.transaction_dt AS last_IBA_transaction_dt
        from transaction_t
        where
            transaction_t.merchant_id = CTE_Merchants.id
            and transaction_t.bank_txt = 'IBA'
        order by transaction_dt desc
    ) AS A2
;

`WITH` keyword must be preceded by a semicolon. – Alex Jun 22 '20 at 03:13 — Alex, Jun 22 '20 at 03:13

Parametizing query for Excel

UPDATE:

END UPDATE

EDIT

2 Answers2

Linked