July 8, 2013 at 11:43 am
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?
July 8, 2013 at 11:51 am
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.
July 8, 2013 at 12:04 pm
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
July 9, 2013 at 7:41 am
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
July 9, 2013 at 8:06 am
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
July 9, 2013 at 9:45 am
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
July 9, 2013 at 4:49 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 9, 2013 at 5:00 pm
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);July 10, 2013 at 1:24 am
If the object already exists the Agent Job will be logged as errorred rather than success.
In this situation, the procedure is simply not going to be there again (at least not until I update the bigger process), so I'm fine just running the create. When it's time to update the process again, I can just delete the job step if it is not needed.
July 10, 2013 at 2:01 am
Or you could just set the step to go to next step on failure....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 11, 2013 at 2:02 am
If the next step is to check why it failed that would be fine.
If it failed because it already exists then the job can continue but if it failed for other reasons it should probably stop.
Not sure if you can specifically identify the cause of the failure but you can at least check for the existance of the function afterwards. If it already existed it will still be there, if it was created it will now exist, if it failed for some other reason then terminate the job.
Alternatively you could have a job step which just checks for the existance of the function and fails the step if it doesn't exist with the create function as the next step.
July 11, 2013 at 8:45 am
The other option is to use dynamic SQL:
IF NOT 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') )
EXEC('CREATE FUNCTION [SCHEMA].[FUNCTION_NAME_HERE]
...');
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 21, 2017 at 4:44 am
Check IF Exist For Function
IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE
object_id = OBJECT_ID(N'[Schema].[function_Name]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [Schema].[function_Name]
Print('function dropped => [Schema].[function_Name]')
END
GO
Check IF Exist for Stored procedure , Function also by clicking below link http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html
May 21, 2017 at 5:19 am
Please note: 4 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply