You can alter a table-valued function (TVF) in SQL Server using the ALTER FUNCTION
T-SQL syntax.
You simply follow ALTER FUNCTION
with the new definition. Basically it’s like using CREATE FUNCTION
except you replace CREATE
with ALTER
.
Example
Here’s an example to demonstrate:
ALTER FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT CatId, CatName, Phone FROM dbo.Cats WHERE CatName = @CatName ); GO
If this function didn’t already exist, and I was creating it for the first time, I’d use the following code:
CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT CatId, CatName, Phone FROM dbo.Cats WHERE CatName = @CatName ); GO
It’s exactly the same, except it begins with CREATE
instead of ALTER
.
Drop and Recreate
Another way to do it is to drop the function and create it again:
DROP FUNCTION IF EXISTS udf_CatsByName_ITVF; GO CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT CatId, CatName, Phone FROM dbo.Cats WHERE CatName = @CatName ); GO
In this case, I used the DIE syntax (Drop If Exists), which means I won’t get an error if the object doesn’t already exist before trying to drop it.
Inline TVFs vs Multi-Statement TVFs
The examples on this page use an inline table-valued function. The same concept applies to multi-statement TVFs.
See Create an Inline Table-Valued Function and Create a Multi-Statement Table-Valued Function for more detailed examples of each one.