Check if Function EXISTS before adding

• dan-572483

  SSCoach

  Points: 15870

  More actions

  July 8, 2013 at 11:43 am

  #277103

  I'm trying to write of script that checks if a certain Function exists, and if it does not, create it. If the Function does exist, then the script should end without touching the function.

  I've tried writing it these ways

  If NOT EXISTS (select * from sysobjects where name = 'FunctionName')

  CREATE FUNCTION --....

  -- and

  IF OBJECT_ID('FunctionName','IF') is null

  CREATE FUNCTION --....

  I have read that CREATE FUINCTION cannot be combined with other statements in the batch and that the CREATE statement must start the batch. I've tried puting the CREATE inside BEGIN/END and parantheses. But I'm still getting syntax errors.

  Is the only option to use the syntax that drops the function when it already exists and create it every time?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 8, 2013 at 11:51 am

  #1630537

  As CREATE PROCEDURE or CREATE FUNCTION must be the first line of a batch (except for comments), the only way to do it is through dynamic SQL. In fact, the scripting utility does that if you enable the option to check for existance before creation.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  July 8, 2013 at 12:04 pm

  #1630541

  Here is the script-form I use:

  IF EXISTS ( SELECT *

  FROM sys.objects

  WHERE object_id = OBJECT_ID(N'[SCHEMA].[FUNCTION_NAME_HERE]')

  AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )

  DROP FUNCTION [SCHEMA].[FUNCTION_NAME_HERE] ;

  GO

  CREATE FUNCTION [SCHEMA].[FUNCTION_NAME_HERE]

  ...

  GO

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Jason-299789

  SSC-Insane

  Points: 21601

  More actions

  July 9, 2013 at 7:41 am

  #1630798

  opc.three (7/8/2013)

  ---

  Here is the script-form I use:

  IF EXISTS ( SELECT *

  FROM sys.objects

  WHERE object_id = OBJECT_ID(N'[SCHEMA].[FUNCTION_NAME_HERE]')

  AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )

  DROP FUNCTION [SCHEMA].[FUNCTION_NAME_HERE] ;

  GO

  CREATE FUNCTION [SCHEMA].[FUNCTION_NAME_HERE]

  ...

  GO

  I use something similar except that I use Name= '<value>' and OBJECT_SCHEMA_NAME(object_Id)='<schema>' with the filter on the Type.

  Is there any performance increase using the OBJECT_ID function over the above mothod or is it much the same?

  _________________________________________________________________________
  SSC Guide to Posting and Best Practices

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  July 9, 2013 at 8:06 am

  #1630814

  I don't typically scrutinize the performance of DDL operations like an IF EXISTS...DROP block but calls to OBJECT_ID and OBJECT_SCHEMA_NAME will likely be equally slow. Joining to sys.schemas will likely yield a faster answer if it's very important to get the best performance in this context. I use OBJECT_ID mainly so I can copy and paste the same thing into the function call and after DROP FUNCTION but it's purely a preference.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Jason-299789

  SSC-Insane

  Points: 21601

  More actions

  July 9, 2013 at 9:45 am

  #1630874

  Thanks OPC, I tend to use the OBJECT_SCHEMA_NAME as a short cut, I might start using the OBJECT_ID going forward.

  _________________________________________________________________________
  SSC Guide to Posting and Best Practices

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  July 9, 2013 at 4:49 pm

  #1631026

  It's a kludge, but when I really needed it, I simply change the db context to tempdb if the function already exists in the main db, then delete the function in tempdb afterward if it was created there:

  USE maindb

  IF EXISTS(SELECT 1 FROM sys.objects WHERE name = N'function_name')

  USE tempdb

  GO

  CREATE FUNCTION dbo.function_name

  ...

  GO

  IF DB_NAME() = N'tempdb'

  DROP FUNCTION function_name

  USE maindb

  IF EXISTS(SELECT 1 FROM sys.objects WHERE name = N'function_name2')

  USE tempdb

  GO

  ...

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 9, 2013 at 5:00 pm

  #1631027

  I know this may seem stupid, but can't you just use the create function statement?

  If it already exists it will not touch the function and if it doesn't exist it will create it, just as you asked

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]