Your system does not have Intel Rapid Start Technology enabledsql - Creating a UDF(User Define Function) if is does not exist and skipping it if it exists - Stack Overflow
to customize your list.
Announcing Stack Overflow Documentation
We started with Q&A. Technical documentation is next, and we need your help.
Whether you're a beginner or an experienced developer, you can contribute.
Hi and thanks for reading this.
I am trying to use the IF EXISTS/IF NOT EXISTS statement to check if an Object exist. Basically I want to skip it if it is there or create it if it is not there.
I have writing the code in two different ways but I get an error: Create function must be the only function in the batch. If I place GO between the statements as Illustrated below, I get another warning: Incorrect Syntax near GO.
Where am I going wrong here?
IF NOT EXISTS
(select * from Information_schema.Routines where SPECIFIC_SCHEMA='dbo'
AND SPECIFIC_NAME = 'FMT_PHONE_NBR' AND Routine_Type='FUNCTION')
/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' +
SUBSTRING(@phoneNumber, 4, 3) + '-' +
SUBSTRING(@phoneNumber, 7, 4)
IF NOT EXISTS
(SELECT name FROM sys.objects WHERE name = 'dbo.FMT_PHONE_NBR')
/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' +
SUBSTRING(@phoneNumber, 4, 3) + '-' +
SUBSTRING(@phoneNumber, 7, 4)
Thanks for checking this out!
90.1k10142162
1,358123059
The easiest way to solve this is actually to delete the function if it already exists, and then re-create it:
/* If we already exist, get rid of us, and fix our spelling */
IF OBJECT_ID('dbo.FMT_PHONE_NBR') IS NOT NULL
DROP FUNCTION FMT_PHONE_NBR
/*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/
CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12))
RETURNS VARCHAR(12)
RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' +
SUBSTRING(@phoneNumber, 4, 3) + '-' +
SUBSTRING(@phoneNumber, 7, 4)
Note the usage of the 'object_id' function in the above.
This is actually a pretty common way to check for the existence of an object, although it is subject to certain constraints.
You can read more about it here:
As I've beaten my head on this brick wall for a long time, I'll toss in two more cents.
As pointed out, yes, it'd be nice to add it only if it isn't already there, but that just not possible in T-SQL without using dynamic SQL... and wrapping your functions, procedures, triggers, views, and maybe even more obscure objects as dynamic statements is just too darn impractical. (Don't ask me to support source code that might contain more than 4 single apostrophes in a row!)
Dropping (if it exists) and (re)creating is a viable solution.
Presumably, if you are rolling out new code, you would want to create the object if it was not already there, and otherwise drop the existing/old code and replace it with the new. (If you might accidentally replace "new" code with "old" code, you have a version control problem, which is a different and much harder topic.)
The real problem is losing information when you drop the old code. What information? The one I often hit is access rights: who has EXECUTE or, for some functions, SELECT rights on the object? Drop and replace, and they're gone. The answer to this, of course, is to script the access rights as part of the deployment script. However if you have a situation where different database-hosting environments have different configurations (logins, domains, groups, etc. etc.), you might be in a situation where you won't and can't know what the existing access rights are on a given instance, so if you just drop and recreate it, existing users may no longer be able to access it. (Extended properties and other bits of esoterica would similarly affected.)
The first and best fix for this is to implement robust security. Set up database roles, assign/associate appropriate permissions to the roles, then you won't have to know who's in the roles--that'd be the job of the environment administrators.
(You'd still have to have something like GRANT EXECUTE on ThisProc to dbo.xxx at the end of your script, but that's not so hard.
If, like me, you (a) haven't been empowered to roll out a good and robust security model, and (b) are lazy and likely to not check the end of a hundreds-of-lines-long stored procedure file for access rights code, you can do something like the following. (This is set for stored procedures, but is adaptible for functions and other objects.)
-- isProcedure
-- IsScalarFunction
(Returns single value)
-- IsTableFunction
(Declared return table structure, multiple statements)
-- IsInlineFunction
(Based on single select statement)
IF objectproperty(object_id('dbo.xxx'), 'isProcedure') is null
Procedure (or function) does not exist, create a dummy placeholder
DECLARE @Placeholder varchar(100)
SET @Placeholder = 'CREATE PROCEDURE dbo.xxx AS RETURN 0'
EXEC(@PlaceHolder)
Configure access rights
GRANT EXECUTE on dbo.xxx TO StoredProcedureUser
ALTER PROCEDURE dbo.xxx
This will:
First check if the procedure exists. If it doesn't, create a "placholder", and set up the appropriate access rights to it
Then, whether or not it existed before the script was run, ALTER and set it with the desired code.
There's also the problem of managing code-based objects (primarily stored procedures) in schemas where the schemas might not exist. I've yet to figure that one out, and if you're lucky, you'll never end up in a similarly oddball situation.
26.8k63564
The error message is exactly right, that CREATE FUNCTION statements must the first in a batch, which means that unfortunately you can't do:
IF [condition]
CREATE FUNCTION
What I usually do in this situation is:
IF object_id('dbo.myFunction') IS NOT NULL
DROP FUNCTION dbo.myFunction
CREATE FUNCTION dbo.myFunction (
Note that I usually use the object_id() function as it's simpler, easier to read, and more robust than EXISTS (SELECT * FROM sys.whatever).
Of course, this solution will only work for you if you're OK with always overwriting any previous definition of the function.
If that isn't OK in your situation, let me know.
Actually this works in 2008
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetTZDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fn_GetTZDate] ()
RETURNS datetime
AS -- WITH ENCRYPTION AS
-- Declare the return variable here
DECLARE @tzadj int, @sysdate datetime
SET @sysdate = getdate()
SET @tzadj = 0
SELECT @tzadj = [tzAdjustment] FROM USysSecurity WHERE [WindowsUserName] = SYSTEM_USER
if @tzadj && 0
SET @sysdate = dateadd(hh, @tzadj, @sysdate)
-- Return the result of the function
RETURN @sysdate
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabled英语好的进!“Dear,if does not have her,you can with me in together?”本人英语不好,但是也觉得这句话满身毛病,超烂的.谁能详细说出它的错误并给个正确的说法啊...我理解..它要表达的意思应该是...”亲爱的，如果没有她，你会／能跟我在一起吗？”
dear,if there is no her,can you be with me?
为您推荐：
其他类似问题
前半句缺个主语，后半句缺个谓语
Darling, if I do not have her, will you be with me?
Darling,if there is no her, can you come in with me?
扫描下载二维码这句英文是这样写吗?if society does not have love heart,
血刺裁决04Y
If the society does not have love and affection (爱心).如果社会上没有爱心.据:Mathews' Chinese-English Dictionary ( Harvard University Press)注:这是一本外国传教士编的中英字典,'爱心'的英译为:love and affection.
为您推荐：
其他类似问题
If there is no love in the world.如果这个世界上没有爱的话，http://new./luciano/tracks/theres-no-love-in-the-world--1061385
If there is no affection or compassion in our society....
扫描下载二维码的海词问答和网友补充：
相关词典网站：