[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Check and Create UDF

Posted on 2009-07-01
8
Medium Priority
?
385 Views
Last Modified: 2012-08-14
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 ....
0
Comment
Question by:allanau20
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 24759935
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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 24760080
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
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 500 total points
ID: 24760699
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 5

Author Comment

by:allanau20
ID: 24765482
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24765556
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
 
LVL 5

Author Comment

by:allanau20
ID: 24765622
THanks. Seems that's more work than using Alter -- permission is set once and you don't have to deal with it.
0
 
LVL 5

Author Comment

by:allanau20
ID: 24765624
Thanks for all your help!
0
 
LVL 5

Author Comment

by:allanau20
ID: 24765642
splitting points ..
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question