creating functions by conditons

Question

I want to create functions if they doesn't exists and update when they exists.

But mssql says that is wrong syntax near function.

 IF NOT EXISTS(SELECT 1 FROM sys.objects where name='CreateJson' AND type='U' )
Begin
Create function [dbo].CreateJson
(
    @String  nvarchar(4000),
    @Param0  SQL_VARIANT = NULL
)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';
    return  replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));
end

end
IF EXISTS(SELECT 1 FROM sys.objects where name='CreateJson' AND type='U' )
Begin

Alter function [dbo].CreateJson
(
    @String  nvarchar(4000),
    @Param0  SQL_VARIANT = NULL

)
returns nvarchar(4000)
as
begin
    declare @Null nvarchar(4) = N'NULL';
     return  replace(@String, N'{0}', cast(isnull(@Param0, @Null) as nvarchar(4000)));
       end
End
Go

How I can make update/create functions by condition?

Instead, drop the function if it exists and then always re-create it. — Gordon Linoff, Jun 07 '17 at 14:59

score 1 · Accepted Answer · answered Jun 07 '17 at 15:02

1

It might be easier to conditionally drop it: (like Sql Server equivalent to Oracle's CREATE OR REPLACE VIEW)

IF OBJECT_ID('[dbo].[CreateJson]') IS NOT NULL
DROP FUNCTION [dbo].[CreateJson]
GO
CREATE FUNCTION [dbo].[CreateJson]
AS
--

answered Jun 07 '17 at 15:02

realbart

3,497
1
25
37

For procedures I should use the same behavior? – John Jun 07 '17 at 15:39
You could do that. I usually do. You could use this approach for everything but tables, (if you don't want to keep your data) – realbart Jun 07 '17 at 15:41
Do you know how I can do it for types cause procedures have references to type it don't see types – John Jun 09 '17 at 13:10

creating functions by conditons

1 Answers1