-- Copyright (c) 2015 gutlib.com - All rights reserved. Terms are in gutlib.sql
-- You are NOT ALLOWED to modify and/or use this code without author permission

exec g.sp 'g.fn'
go
alter procedure g.fn
    @name sysname
   ,@body nvarchar(max)=null
   ,@drop bit=null
   ,@ret nvarchar(max)=null -- return type to avoid incompatible function error when altering
as
declare @cmd nvarchar(max)
if @drop is null set @drop=0
set @name = replace(@name,'''','''''')
if @ret is null set @ret = 'int'

if @drop = 1
   set @cmd = 'drop function ' + @name
else
   set @cmd = 'create function ' + @name + ' () returns ' + @ret + '
      as begin
         -- simulating error because RAISERROR is not allowed inside functions
         return '
            + iif(@ret like '%table%'
               ,''
               ,'cast(''g.fn.err.2.' + @name + '.err.1: not implemented.'' as int)'
            ) + '
      end'
if not exists(select 1 from g.obj where name = @name) begin
   if @drop = 0
      exec(@cmd)
end else
   if @drop = 1
      exec(@cmd)

if @body is not null and @drop=0
   exec(@body) -- @body must have alter function statement


go ------------------------------- unit tests ---------------------------------
exec g.fn 'g.test_fn'
if not exists(select 1 from g.obj where name='g.test_fn')
   RAISERROR ('g.fn.err.3' ,16 ,1) WITH NOWAIT

exec g.fn 'g.test_fn','
   alter function g.test_fn (@i int)
      returns int as begin
         return @i+1
      end
'
exec('
if g.test_fn(1) != 2
   RAISERROR (''g.fn.err.4'' ,16 ,1) WITH NOWAIT
')
exec('exec g.fn ''g.test_fn'',@drop=1')
