CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
John
John

Reputation: 81

creating functions by conditons

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?

Upvotes: 0

Views: 46

Answers (1)

realbart
realbart

Reputation: 3994

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

Upvotes: 1

Related Questions