Toad World® Forums

Tune SP with a Use-Defined Table types


#1

Invalid SQL statement.

Table does not exist.

Invalid object name ‘@ItemTable b’.

Possible causes of this error are:

  • You are not pointing to the user and the database where the objects referenced by the SQL are located.

  • You are using T-SQL code or more than one SQL statement. Only a single SELECT/INSERT/DELETE/UPDATE can be processed.

  • Your SQL statement has object references within quotes. You need

Here is the sp.

CREATE PROCEDURE [dbo].[GetPriceByItems]
@MarketUnitCode CHAR (2), @ItemTable [dbo].[ItemTable] READONLY
AS

SELECT
, DepDate
, Dur
, PaxPrice
, Price
, Disc_oth
, RoomTp
FROM DBO.VW_TPRILO a
JOIN @ItemTable b ON (a.Wb.ItemID) = b.ItemID)
WHERE a.Mucd = @MarketUnitCode
ORDER BY price

I’m trying to tune a SP with a Use-Defined Table type but I can’t get it to work.
What am I dowing wrong?, I get the below, hoe do I define the Use-Defined Table?

Therefore, the objects cannot be found.For multiple SQL statements or T-SQL code, first use the SQL Scanner module to extract the SQL statements.to enable the “set quoted identified on” option in the Preferences - > Database Settings. - The SQL statement uses a temporary table. You must create the temporary table using the User-Defined Temp Table function.

END

The Use-Defined Table types

CREATE TYPE [dbo].[ItemTable] AS TABLE(
[ItemID] [int] NOT NULL
)


#2

I believe Table valued function is not supported in the latest version of optimizer.

the most possible workaround is to insert to a physical table to optimize. :slight_smile:


#3

Yes, User-defined table type is not supported. To optimize this SQL, you can either create a physical table or a temporay table. We will consider support User-defined table type in the future release.

Thank you very much for using Quest SQL Optimizer for SQL Server. Your message is valuable to us.