Eror While Executing a dynamic queries in a variable

I’m executing a dynamic query from a variable but it always says:

Could not find stored procedure

I have tried to use simpler queries like set @query = 'select * from [table_name]' but it gives the same error.

CREATE TABLE #test
(
    id INT IDENTITY(1, 1) NOT NULL,
    name VARCHAR(200)
)

DECLARE @TRIGER_NAME VARCHAR(200)
DECLARE @V_MAX INT
DECLARE @V_MIN INT
DECLARE @QUERY VARCHAR(MAX)

SELECT @V_MAX = MAX(id)
FROM #test

SELECT @V_MIN=Min(id)
FROM #test

WHILE @V_MIN <= @V_MAX
BEGIN
    SELECT @TRIGER_NAME = name
    FROM #test
    WHERE id = @V_MIN

    SET @QUERY = 'DROP TRIGGER '+ @TRIGER_NAME;
    EXEC @query

    SET @V_MIN = @V_MIN+1
END

In my temporary table is the list of all my trigger name so the expected output I want to get is Command(s) completed successfully. Not ‘Could not find stored procedure’, because I supposed to executing a dynamic queries not a stored procedure.

Answer

Instead EXEC @query use, EXEC sp_executesql @query. From the docs:

Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

Or if you want to use EXEC, change it to:

EXEC (@query)   

From the documentation you have:

Execute a character string { EXEC | EXECUTE }
( { @string_variable | [ N ]’tsql_string’ } [ + …n ] )
[ AS { LOGIN | USER } = ‘ name ‘ ] [;]

which means your @string_variable or string must be wrapped in ().