Creating a Table valued Function if doesn't exist dynamically and altering it normally

Question

I am working on creating a table-valued function but before creating it I would like to check if it exists and if it doesn't exist then create the function using dynamic script and then alter it normally.

IF  NOT EXISTS 
(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[region]') 
 AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
BEGIN
    EXEC dbo.sp_executesql 
         @statement = N' CREATE FUNCTION [dbo].[region]() 
                         RETURNS TABLE AS  BEGIN RETURN ''A'' END'
 GO


ALTER FUNCTION dbo.region(@dd datetime)
--
GO

But, above script threw me an error Incorrect syntax near the keyword 'RETURN'.

Answer 1

i think you have to write as follows:

CREATE FUNCTION [dbo].[region]() 
                         RETURNS TABLE AS return ( select  'A')

and in case you want to return a variable table just to insert into it before returning it as a result, you can use the following,

CREATE FUNCTION [dbo].[region]() 
RETURNS @table_buffer TABLE
(
TransDate datetime,
Company nvarchar(4),
RECID bigint
) 
AS
begin
  insert into @table_buffer select -- here you will complete it according to your need
  return
end