CODEWITHSUNDEEP
sqluser-defined-functionssql-server-2014dynamic-sql
DoIt
DoIt

Reputation: 3428

Creating a Function if doesn't exist and alter the function if it already exists

I am working on creating a scalar 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. I got this working for a stored procedure but I couldn't do the same with a function.

my procedure is as follows

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

ALTER PROCEDURE  [dbo].[region](---)
AS
---
END

I tried to follow the same approach for a scalar valued function as follows

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

GO 

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

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

Upvotes: 3

Views: 2617

Answers (1)

benni_mac_b
benni_mac_b

Reputation: 8877

You need to specify parameters (even if none) and a return type for the function

EXEC dbo.sp_executesql @statement = N' CREATE FUNCTION [dbo].[region]() RETURNS VARCHAR AS  BEGIN RETURN ''A'' END'

Upvotes: 2

Related Questions