Check and Create UDF

HI Experts,

This should be easy. How to check if udf exist, if not then create a blank udf.

IF NOT EXISTS (select name
from sysobjects
where type in ('FN', 'IF', 'TF')
  and name = 'Some_udf')
      EXEC('CREATE FUNCTION dbo.Some_udf AS RETURN 0')
GO

-- The Alter Statement goes here

ALTER FUNCTION [dbo].Some_udf
(
  @blah01 CHAR(13),
  @blah02 CHAR(2)
)  
RETURNS CHAR(2)
AS
BEGIN
END
RETURN ....
LVL 5
allanau20Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
IF NOT EXISTS (select name from sysobjects where type in ('FN', 'IF', 'TF')
  and name = 'Some_udf')
EXEC('CREATE FUNCTION dbo.Some_udf()returns int  as BEGIN  RETURN(0) END')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Why not just delete if it exists:
IF EXISTS (select name
from sysobjects
where type in ('FN', 'IF', 'TF')
  and name = 'Some_udf')
      DROP FUNCTION dbo.Some_udf
GO
 
-- The Alter Statement goes here
 
CREATE FUNCTION [dbo].Some_udf
(
  @blah01 CHAR(13), 
  @blah02 CHAR(2)
)  
RETURNS CHAR(2)
AS
BEGIN
-- function code here
RETURN
END

Open in new window

0
RiteshShahCommented:
one short way to check existence of the function or any other object.


IF OBJECT_ID('Some_udf') is not null  DROP FUNCTION dbo.Some_udf
GO
 
CREATE FUNCTION [dbo].Some_udf()  
returns int
AS
BEGIN
return 1
END


0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

allanau20Author Commented:
Thanks all for your replies.
correct me if i'm wrong; one reason why i don't want to drop is because the object will lose it's permission if dropped.
0
Kevin CrossChief Technology OfficerCommented:
Correct, so you can check for non-existence and create as you were but then you would have to explicitly GRANT permissions in that case; therefore, you can always drop and then after creation add GRANT statements you need.
0
allanau20Author Commented:
THanks. Seems that's more work than using Alter -- permission is set once and you don't have to deal with it.
0
allanau20Author Commented:
Thanks for all your help!
0
allanau20Author Commented:
splitting points ..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.