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'.

remove ''A'' and add table definition, i.e. `returns table (c int) as begin return end` — artm, Feb 11 '16 at 01:36
That way I cannot later my function as I have multiple columns in the table thats being returned which throws incompatible type error when I try to alter — DoIt, Feb 11 '16 at 15:49
Drop the function first and re-create it instead of trying to alter it — artm, Feb 11 '16 at 22:26

Boody · Answer 1 · 2016-02-11T14:11:31.280

2

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

edited Feb 11 '16 at 14:11

answered Feb 11 '16 at 06:05

Boody

144
7

You also need to specify column names for the fields you select this way. – TT. Feb 11 '16 at 09:35
you only need to define the table columns when you want to return a variable table, and you usually use a variable table to insert into before returning its records, for example, please read my second answer. – Boody Feb 11 '16 at 14:07
1

I think you misunderstood me, your creation statement **will fail** with the following error message: *CREATE FUNCTION failed because a column name is not specified for column 1.*. The create statement should read `CREATE FUNCTION [dbo].[region]() RETURNS TABLE AS return ( select 'A' AS A)` – TT. Feb 11 '16 at 14:10
ahh, you mean to give the columns aliases ? OK, anyways, i edited my answer for the other option. – Boody Feb 11 '16 at 14:18
is it mandatory to specify all the columns when I create the function dynamically, can I not just alter and add the columns later? because I get incompatible object type error – DoIt Feb 11 '16 at 15:44
I don't think you can manipulate the table or its columns later since it is similar to the temp table which is created everytime you call the table valued function and dropped when that calling session finishes. Thus, you can create it as table dynamically instead of creating a tablu valued function. – Boody Feb 12 '16 at 22:05

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

1 Answers1