What does command EXECUTE stand for?

Question

I'm rather new using SQL and I have some difficulties understanding old queries and re-use it. Especially the utility of EXECUTE and OPENQUERY commands when working on linked servers.

The code I'm using is functional because I didn't create it. But on some part I don't understand exactly how it works. I would need to understand it better to be able to create my own requests.

I've read documentation like this (https://learn.microsoft.com/fr-fr/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017) but I still cannot undertand why EXEC is needed when working on a linked server.

EXEC('
SELECT  Cell_id,
        CAST(Trafic_Voix as float) as Trafic_Voix

        FROM OPENQUERY(myperf_ora,''

            SELECT  Cell_id,
                    MAX( (TFTRALACC+THTRALACC)/360 ) as Trafic_voix

                    FROM VIRTUO.ERI_CELL_TF_CHAN_FR_TAB_V
                    WHERE tstamp  BETWEEN '''''+@DATE_DE_DEBUT+''''' AND '''''+@DATE_DE_FIN+'''''

                    GROUP BY Cell_id                        
                                '')
    ')

If that changes anything I'm using Microsoft SQL Server Management Studio.

When I take off EXEC from the query, it doesn't work anymore and shows me error messages. I'd like to know why using this command is mandatory?

Same thing for OPENQUERY

`EXECUTE` executes a stored procedure or query. Doesn't the documentation cover this? — Gordon Linoff, May 06 '19 at 10:33
I did read the documentation (https://learn.microsoft.com/fr-fr/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017) but i's not clear for me why i have to use EXECUTE in my query — Amaury Piette, May 06 '19 at 10:43
Dynamic SQL is issued here because [OPENQUERY does not accept parameters](https://stackoverflow.com/questions/3378496/including-parameters-in-openquery). An alternative may be to query the remote table directly using a 4-part name. — Dan Guzman, May 06 '19 at 10:43
Thanks, it may be the point. I don't know the 4-part name of the table i want to reach. I konw [MPERF_ORA].[XXX].[YYY].[ERI_CELL_TF_CAN_FR_TA_V]. Which XXX and YYY I don't know — Amaury Piette, May 06 '19 at 12:12

score 0 · Answer 1 · answered May 06 '19 at 10:44

It is running dynamic SQL queries. This code creates an SQL query like SELECT Cell_id, MAX( (TFTRALACC+THTRALACC)/360 ) as Trafic_voix ..., and executes it. The result of the query is accessible by the myperf_ora cursor.

Why they needed it? Because the DATE_DE_DEBUT and DATE_DE_FIN variables are parameters in the query, and the author found that it is the easiest to include them into the query this way.

Certainly there are more elegant ways to do it.

Bart Hofland · Answer 2 · 2019-05-06T12:55:05.500

EXEC (or EXECUTE) are used to execute dynamically generated SQL statements.

The query given here is not that complex. So it is possible to remove the EXEC. But you have to replace double single quotes ('') in the (concatenated) string(s) with single single quotes (').

So basically you want to execute this:

SELECT
    Cell_id,
    CAST(Trafic_Voix as float) as Trafic_Voix
FROM
    OPENQUERY(myperf_ora,'
        SELECT Cell_id,
               MAX( (TFTRALACC+THTRALACC)/360 ) as Trafic_voix
        FROM VIRTUO.ERI_CELL_TF_CHAN_FR_TAB_V
        WHERE tstamp  BETWEEN ''' + @DATE_DE_DEBUT + ''' AND ''' + @DATE_DE_FIN + '''
        GROUP BY Cell_id
    ')

Edit (in response to comment):

Actually... it is not possible. Sorry. :(

It seems that the OPENQUERY rowset function only accepts string literals for its second parameter. That would make the EXEC statement necessary: that way, the dynamically generated SQL query will contain a string literal for the second parameter of the OPENQUERY function.

Dan Guzman already added a commment to your original post regarding this issue.

Thanks for the proposal. But When I try your code I have error messages : "Msg 102, Level 15, State 1, Line 28 Incorrect syntax near '+'." While I'm quite sure the syntax is correct — Amaury Piette, May 06 '19 at 12:13

What does command EXECUTE stand for?

2 Answers2