Problem altering inline table function with SCHEMABINDING
ddesc
Posts: 3
Hi,
I use SQL Packager 6.4.0.8, and SQL Server 2008 R2.
I'm having a few issues creating an update package for a database we deploy. It seems that there are some issues with the syntax of the ATLTER FUNCTION statement in the generated script file under certain circumstances.
When upgrading the table function the first thing your script does is remove SCHEMABINDING from the existing function definition. It uses the following statement to do this.
Unfortunately this statement is incorrect. It uses WITH instead of AS which is incorrect syntax. Why this is happening had me scratching my head for a little while but it seems this is caused by the way the function was originally defined.
When the function was first written it was defined like this.
You will notice the AS has been omitted from the function definition. It seems that SQL Server regards the AS as optional if you use WITH SCHEMABINDING in the definition. Therefore the syntax of the statement above is accepted by the database.
If I change the original definition of the table function to include the AS after the WITH SCHEMABINDING then SQL Packager generates the scripts for the upgrade without syntax errors.
I suspect the omission of AS in the original function definition may not be best practise, but as SQL Server accepts its omission it is therefore valid so I feel your product should support it.
Hope this makes sense,
Cheers
Sam.
I use SQL Packager 6.4.0.8, and SQL Server 2008 R2.
I'm having a few issues creating an update package for a database we deploy. It seems that there are some issues with the syntax of the ATLTER FUNCTION statement in the generated script file under certain circumstances.
When upgrading the table function the first thing your script does is remove SCHEMABINDING from the existing function definition. It uses the following statement to do this.
PRINT N'Removing schema binding from [dbo].[ftrdsh_getParameterRecord]'
GO
ALTER FUNCTION [dbo].[myTableFunction](@pParameter NVARCHAR(MAX))
RETURNS TABLE
WITH
RETURN (
-- Implementation of function
); -- myTableFunction
GO
Unfortunately this statement is incorrect. It uses WITH instead of AS which is incorrect syntax. Why this is happening had me scratching my head for a little while but it seems this is caused by the way the function was originally defined.
When the function was first written it was defined like this.
CREATE FUNCTION [dbo].[myTableFunction](@pParameter NVARCHAR(MAX))
RETURNS TABLE
WITH SCHEMABINDING
RETURN (
-- Implementation of function
);
GO
You will notice the AS has been omitted from the function definition. It seems that SQL Server regards the AS as optional if you use WITH SCHEMABINDING in the definition. Therefore the syntax of the statement above is accepted by the database.
If I change the original definition of the table function to include the AS after the WITH SCHEMABINDING then SQL Packager generates the scripts for the upgrade without syntax errors.
I suspect the omission of AS in the original function definition may not be best practise, but as SQL Server accepts its omission it is therefore valid so I feel your product should support it.
Hope this makes sense,
Cheers
Sam.
Comments
Thanks for pointing that out. SQL Packager, when creating an upgrade package, leaves the WITH behind and doesn't add an AS clause, and that seems to cause the problem. The bug reference number is SPA-594.
Thanks for this.
Cheers
Sam.