Solved

Check and Create UDF

Posted on 2009-07-01
8
378 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
8 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 125 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 125 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 59

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now