Return
-
Title
Cannot optimize query that uses variables of type table. -
Description
A script was entered in Toad Editor and user has tried to optimize using SQL Optimizer. The query includes a join with a table type variable. Optimizer shows an error when user attempting to rewrite the query.
"Invalid SQL statement.
Only one SELECT/INSERT/UPDATE/DELETE/MERGE statement is allowed." -
Cause
1. User has tried to add multiple statements in Optimizer for rewrite of query:
Declare...
Select ....
2. Optimizer does not support undefined table type variables. -
Resolution
1. SQL Optimizer for SQL Server supports only one statement at time, so multiple statements are not allowed in SQL optimizer to optimize query. Modify the script so that only one statement is entered.
2. To execute @table type variable that requires a separate Declare statement, user needs to create a temporary table by using the “Temp Tables" option.
Refer to attached document for detailed steps. -
Attachments