March 25, 2015 at 8:10 pm
Hello,
Let's say I have a function named MyCustomFunction and I want to ensure that it exists in the database. Let's say I have a create script for the function. I want the script to be runnable multiple times if needed. A common way to do this is to check for an object_id at the top of the function like this:
IF OBJECT_ID('MyCustomFunction') IS NULL
DROP FUNCTION MyCustomFunction
GO
CREATE FUNCTION MyCustomFunction...
But is there a more elegant way to do this? For example, instead of dropping and recreating the function, is there a way to simply exit from the script and do nothing if the function already exists? Something like this:
IF OBJECT_ID('MyCustomFunction') IS NULL RETURN
GO
CREATE FUNCTION MyCustomFunction...
March 26, 2015 at 12:13 am
sqlguy-736318 (3/25/2015)
Hello,Let's say I have a function named MyCustomFunction and I want to ensure that it exists in the database. Let's say I have a create script for the function. I want the script to be runnable multiple times if needed. A common way to do this is to check for an object_id at the top of the function like this:
IF OBJECT_ID('MyCustomFunction') IS NULL
DROP FUNCTION MyCustomFunction
GO
CREATE FUNCTION MyCustomFunction...
But is there a more elegant way to do this? For example, instead of dropping and recreating the function, is there a way to simply exit from the script and do nothing if the function already exists? Something like this:
IF OBJECT_ID('MyCustomFunction') IS NULL RETURN
GO
CREATE FUNCTION MyCustomFunction...
Quick suggestion, use BEGIN and END with the IF clause
😎
IF OBJECT_ID('dbo.MyCustomFunction') IS NULL
BEGIN
-- CREATE SCRIPT..... dbo.MyCustomFunction
END
Another thing in the code which is worth commenting on is the absence of the schema name, suggest that you ALWAYS use schema qualified names, as objects with the same name can exist in different schemas.
March 26, 2015 at 7:24 am
Eirikur Eiriksson (3/26/2015)
sqlguy-736318 (3/25/2015)
Hello,Let's say I have a function named MyCustomFunction and I want to ensure that it exists in the database. Let's say I have a create script for the function. I want the script to be runnable multiple times if needed. A common way to do this is to check for an object_id at the top of the function like this:
IF OBJECT_ID('MyCustomFunction') IS NULL
DROP FUNCTION MyCustomFunction
GO
CREATE FUNCTION MyCustomFunction...
But is there a more elegant way to do this? For example, instead of dropping and recreating the function, is there a way to simply exit from the script and do nothing if the function already exists? Something like this:
IF OBJECT_ID('MyCustomFunction') IS NULL RETURN
GO
CREATE FUNCTION MyCustomFunction...
Quick suggestion, use BEGIN and END with the IF clause
😎
IF OBJECT_ID('dbo.MyCustomFunction') IS NULLBEGIN
-- CREATE SCRIPT..... dbo.MyCustomFunction
END
Another thing in the code which is worth commenting on is the absence of the schema name, suggest that you ALWAYS use schema qualified names, as objects with the same name can exist in different schemas.
This doesn't work. Create has to be the only statement in a batch. You would have to do that with dynamic sql inside your if block.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2015 at 7:28 am
Sean Lange (3/26/2015)
This doesn't work. Create has to be the only statement in a batch. You would have to do that with dynamic sql inside your if block.
Good point, this is how I normally do this
😎
DECLARE @CREATE_FUNCTION_STR NVARCHAR(MAX) = N'-- CREATE SCRIPT..... dbo.MyCustomFunction';
IF OBJECT_ID('dbo.MyCustomFunction') IS NULL
BEGIN
EXEC (@CREATE_FUNCTION_STR);
END
March 26, 2015 at 9:55 am
Some people do something like this:
--If object doesn't exist, create a dummy object
IF OBJECT_ID('dbo.InexistentFunction') IS NULL
EXEC('CREATE FUNCTION InexistentFunction()
RETURNS int
AS
BEGIN
RETURN 0
END')
GO
--Simply alter the object to get the correct definition.
ALTER FUNCTION dbo.InexistentFunction
(
@MyParam int
)
RETURNS date
AS
BEGIN
RETURN DATEADD( dd, @MyParam, GETDATE())
END
GO
--Test
SELECT dbo.InexistentFunction(10)
--Cleanup (just for this example)
DROP FUNCTION InexistentFunction
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply