Create Function Within IF statement. 1

[grnzbra]

I need a function within a script. At any time when this script is run, the function may or may not exist. I have tried writing it in the form of
IF EXISTS

DROP FUNCTION​

GO
CREATE FUNCTION
GO
This gives an error saying the function already exists (I guess because they are in separate statements)
I have also tried
IF NOT EXISTS

CREATE FUNCTION​

GO
This gives me an error that says the create function must be the only item in the statement.

Is there any way to check for the existence of the function and delete it followed by a CREATE FUNCTION statement that will run if the function already exists or create the function within a IF NOT EXISTS statement?

 

[bborissov]

IF EXISTS(
   EXEC sp_executesql N'DROP FUNCTION xxxxx'

IF NOT EXISTS(..
   EXEC sp_executesql N'CREATE FUNCTION xxxx ...
                       '

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server

 

[Chris Miller]

The syntax is

DROP FUNCTION ... IF EXISTS
GO
CREATE FUNCTION...
AS
...

The GO is typical for SSMS and turns one script into two batches. If you execute this from a client language you run two executes, one for the DELETE ... IF EXISTS and one for the CREATE, GO is nothing you can actually execute outside of an SSMS query window, wasn't that already discussed thousands of times?

Mainly you got the syntax wrong, simply look into the DROP FUNCTION documentation:

https://learn.microsoft.com/en-us/s...p-function-transact-sql?view=sql-server-ver16

The IF EXISTS part is optional, but is exactly what you need when you don't know whether the function exists. And the whole thing could also be skipped, if you keep functions once you defined them, there's no need to always drop and recreate functions unless you dycamically create code of a function and still give it the same name. I wouldn't have a good idea for such a construct, I'd perhaps rather use a CTE or other adhoc mechanisms and not define a function at all. If you define something for a single time usage, that's a waste of time.



Chriss

 

[grnzbra]

Thank you both very much. It works fine now.
Unfortunately, I need to run these script against various databases that are sent to me, so I need to be able to create the function if it isn't there, but not have the script choke if it is there.

 

[mikrom]

Isn't it possible to use the statement CREATE OR REPLACE FUNCTION ?