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) AS BEGIN RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + SUBSTRING(@phoneNumber, 4, 3) + '-' + SUBSTRING(@phoneNumber, 7, 4) END GO
Or this:
IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = 'dbo.FMT_PHONE_NBR') GO /*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/ CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12)) RETURNS VARCHAR(12) AS BEGIN RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + SUBSTRING(@phoneNumber, 4, 3) + '-' + SUBSTRING(@phoneNumber, 7, 4) END GO
Thanks for checking this out!
A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its creator.
SQL Server Inline Table-Valued User Defined Function (UDF) Example. A table-valued udf returns a rowset instead of just a single value as is the case for a scalar-valued udf. An inline table-valued udf is a kind of udf that depends on just one select statement.
A user-defined function (UDF) is a routine that you write in SPL or in a language external to the database, such as C or Java™, and that returns a value to its calling context.
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 GO /*CREATE FUNCTION TO FORMAT PHONE NUMBERS*/ CREATE FUNCTION [dbo].[FMT_PHONE_NBR](@phoneNumber VARCHAR(12)) RETURNS VARCHAR(12) AS BEGIN RETURN SUBSTRING(@phoneNumber, 1, 3) + '-' + SUBSTRING(@phoneNumber, 4, 3) + '-' + SUBSTRING(@phoneNumber, 7, 4) END GO
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: OBJECT_ID
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) -- IsView IF objectproperty(object_id('dbo.xxx'), 'isProcedure') is null BEGIN -- 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 END GO ALTER PROCEDURE dbo.xxx (etc.) GO
This will:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With