CODEWITHSUNDEEP
sqlexcelt-sqlopenquery
Bud.HA
Bud.HA

Reputation: 31

Adding parameters to OPENQUERY SQL through an Excel cell

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?

Upvotes: 2

Views: 909

Answers (2)

Christian Delos Reyes
Christian Delos Reyes

Reputation: 28

Try this.

EXECUTE sp_executesql @openquery

Upvotes: 0

Slawomir Cieslinski
Slawomir Cieslinski

Reputation: 635

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);

Upvotes: 1

Related Questions