Common table expressions returned by User Defined Functions may fail

Category: Troubleshooting & error messages
Date: 07 Apr 2009
Product: SQL Compare
Versions: 3.x-6.x
Fix Version: 7.0

When synchronizing a database schema which contains a User-Defined Function that returns a Common Table Expression, SQL Compare may not properly detect object dependencies on the UDF, possibly resulting in objects being scripted out of order or without the necessary dependent objects in the dependency chain.

When migrating a UDF that returns a Common Table Expression table as in the example below, SQL Compare does not detect the dependencies of the CTE object. Note that when you view the SQL script from the SQL Compare synchronization wizard, only the UDF code is displayed, not the ProductionCategory or Product tables, even though the CTE depends on these tables. CTEs are generally supported in SQL Compare: only this particular syntax will cause SQL Compare to produce a SQL synchronization that may result in an incomplete script or objects that are scripted in incorrect dependency order. This is a bug that will be fixed in a future version of SQL Compare.

If the UDF returns a table derived from the CTE (selecting the CTE table INTO an object DECLARED AS TABLE), the dependencies are detected properly.

In order to reproduce the issue, run the following script in AdventureWorks to create the UDF, then use SQL Compare to migrate only this UDF and all dependent objects to an empty database.

CREATE FUNCTION [dbo].[ufnInlineCTETableTest]()
RETURNS Table
AS
RETURN
(
WITH ProductAndCategoryNamesOverTenDollars ([Name], CategoryName,
ListPrice) AS
(
   SELECT
      p.[Name],
      c.[Name],
      p.ListPrice
   FROM [Production].Product p
      INNER JOIN [Production].ProductCategory c ON
         c.ProductCategoryID = p.[ProductSubcategoryID]
   WHERE p.ListPrice > 10.0
)
select ListPrice from ProductAndCategoryNamesOverTenDollars
);