CODEWITHSUNDEEP
sql-serverstored-procedures
Bizhan
Bizhan

Reputation: 17085

How to terminate SQL script before creating procedure or function

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] ()
...

Upvotes: 2

Views: 88

Answers (4)

Coder1991
Coder1991

Reputation: 735

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

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46193

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.

Upvotes: 1

Hamed Nikzad
Hamed Nikzad

Reputation: 658

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'

Upvotes: 1

Chris
Chris

Reputation: 387

CREATE FUNCTION IF NOT EXISTS

Create the function first with this key phrase

Upvotes: -1

Related Questions