How to terminate SQL script before creating procedure or function

Question

I have the following script in SQL Server 2014 for creating a scalar function named GetFiscalPeriod. The script must check for the existence of the function by its name before creating it.

USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (select * from dbo.sysobjects where 
     id = object_id(N'[dbo].[GetFiscalPeriod]') 
     and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
--want to terminate the whole batch
RETURN --doesn't work the way I want
--seems to terminate IF batch only
GO

CREATE FUNCTION [dbo].[GetFiscalPeriod] ()
Returns INT
AS
BEGIN
    RETURN (select MAX(Id) from dbo.__FiscalPeriod__);
END

I want it to terminate the whole thing as it reaches inside IF body. (RETURN)

The problem is no matter how I change the code, either it jumps to CREATE FUNCTION giving this error:

There is already an object named 'GetFiscalPeriod' in the database.

Or giving this syntax error (when I try to put CREATE FUNCTION in the IF clause):

'CREATE FUNCTION' must be the first statement in a query batch.

Question is:

Is there anyway to tell SQL to ignore the rest of the script when the object name exists?

Note:

I used to drop the function beforehand, and it works. But I don't want to drop and recreate everything every time.

...
IF EXISTS (select * from dbo.sysobjects where 
     id = object_id(N'[dbo].[GetFiscalPeriod]') 
     and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[GetFiscalPeriod] --works
GO

CREATE FUNCTION [dbo].[GetFiscalPeriod] ()
...

Make the create statement dynamic SQL, and execute using sp_executesql — TT.
– TT., Commented Jul 2, 2017 at 14:27
This script is not a single batch but multiple batches (separated by GO separators). — Dan Guzman
– Dan Guzman, Commented Jul 2, 2017 at 14:28

Hamed Nikzad · Accepted Answer · 2017-07-02 14:40:52Z

1

You can check for existence, if existed ALTER else CREATE: you must exec query as string.

IF not EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetFiscalPeriod]') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
execute sp_executesql N'
CREATE FUNCTION [dbo].[GetFiscalPeriod] () 
Returns INT
AS
BEGIN
    RETURN (select MAX(Id) from dbo.__FiscalPeriod__);
END'
else
N'
ALTER FUNCTION [dbo].[GetFiscalPeriod] () 
Returns INT
AS
BEGIN
    RETURN (select MAX(Id) from dbo.__FiscalPeriod__);
END'

answered Jul 2, 2017 at 14:40

Hamed Nikzad

67610 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Guzman · Accepted Answer · 2017-07-02 14:55:02Z

1

Without dynamic SQL, you'll need to raise an error to stop the calling application from running the remainder of the script. This can be done with the -b command-line argument in the case of SQLCMD:

SQLCMD -S YourServer -d YourDatabase -E -iYourScript.sql -I -b

The T-SQL within the executed script to raise the error and terminate execution:

IF OBJECT_ID(N'dbo.GetFiscalPeriod', 'FN') IS NOT NULL
    RAISERROR('Function GetFiscalPeriod already exists', 16, 1);

If you are using SQL Server 2016 SP1 or later, you can use CREATE OR ALTER to avoid dropping the object beforehand. It seems to me you would want to recreate the function even if it exists since it may be different.

edited Jul 2, 2017 at 14:55

answered Jul 2, 2017 at 14:48

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

Comments

Coder1991 · Accepted Answer · 2017-07-02 14:58:04Z

1

You can do that check in the following way using dynamic sql.

declare @var nvarchar(max) 
  ='CREATE FUNCTION [dbo].[GetFiscalPeriod] ()
  Returns INT
  AS
  BEGIN
  RETURN (select MAX(Id) from dbo.__FiscalPeriod__);
  END' 
IF Not EXISTS (funname) --function name for which the existence should be 
begin                   --checked
exec sp_executesql @var            
end
GO

edited Jul 2, 2017 at 14:58

answered Jul 2, 2017 at 14:44

Coder1991

7351 gold badge5 silver badges9 bronze badges

Comments

Chris · Accepted Answer · 2017-07-02 14:12:23Z

-1

CREATE FUNCTION IF NOT EXISTS

Create the function first with this key phrase

answered Jul 2, 2017 at 14:12

Chris

3875 silver badges18 bronze badges

1 Comment

Bizhan Over a year ago

thanks for answering but this doesn't seem to be sql server syntax

Collectives™ on Stack Overflow

How to terminate SQL script before creating procedure or function

Note:

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Note:

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related