Adding parameters to OPENQUERY SQL through an Excel cell

Question

I have a SQL query which works in Excel but it uses the following code.

SET QUOTED_IDENTIFIER OFF

SELECT * 
FROM OPENQUERY (
    INSQL, 
    "SELECT DateTime = convert(nvarchar, DateTime, 21), [TagName]
     FROM WideHistory
     WHERE wwRetrievalMode = 'Cyclic'
           AND wwCycleCount = 100
           AND wwVersion = 'Latest'
           AND DateTime >= '20161122 08:33:00.000'
           AND DateTime <= '20161122 08:38:00.000'"
)

I am trying to add the DateTime through an Excel cell. Reading through forums, it mentioned that parametrizing OPENQUERY is not as straightforward as normal SQL queries in Excel.

Is there anything I can do to link a cell in Excel where I can change these parameters from?

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

The docs: OPENQUERY (Transact-SQL):

Syntax OPENQUERY ( linked_server ,'query' ). OPENQUERY does not accept variables for its arguments.

' query ' -Is the query string

Create query as string and execute

DECLARE @dt_start DATETIME = '2016-11-22 08:33:00.000';
DECLARE @dt_end DATETIME = '2016-11-22 08:38:00.000';

DECLARE @query VARCHAR(MAX);
DECLARE @openquery VARCHAR(MAX);

SELECT @query = 'SELECT DateTime = convert(nvarchar, DateTime, 21), [TagName]
                 FROM WideHistory
                 WHERE wwRetrievalMode = ''Cyclic''
                 AND wwCycleCount = 100
                 AND wwVersion = ''Latest''
                 AND DateTime >= ''' + CONVERT(VARCHAR(23), @dt_start, 121) + '''
                 AND DateTime <= ''' + CONVERT(VARCHAR(23), @dt_end, 121) + '''';

 --SELECT @query; 

SELECT @openquery = 'SELECT * FROM OPENQUERY (INSQL, ''' + REPLACE(@query, '''', '''''') + ''')';

 --SELECT @openquery;

EXEC (@openquery);

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 22 '16 at 13:25

Slawomir Cieslinski

625
5
14

The above query works but since I have little programming experience, can you please explain how to set @dt_start using VBA/Excel from an excel cell from a user's point of view. I don't want the user to have to go change the code. Thanks – Bud.HA Dec 06 '16 at 23:56
This code should be inside store procedure. Stored procedures have ability to pass parameters. – Slawomir Cieslinski Dec 08 '16 at 11:16
dt_start DATETIME = '2016-11-22 08:33:00.000'; dt_end DATETIME = '2016-11-22 08:38:00.000'; Hard coded values were only for testing. Those variables should be stored procedure parameters. When you execute stored procedure from VBA, you can pass cells values as parameters. – Slawomir Cieslinski Dec 08 '16 at 11:31

score 0 · Answer 2 · answered Nov 24 '16 at 08:42

0

Try this.

EXECUTE sp_executesql @openquery

answered Nov 24 '16 at 08:42

Christian Delos Reyes

28
3

Adding parameters to OPENQUERY SQL through an Excel cell

2 Answers2