Solved

Create function if not exists in Sql Server

Posted on 2011-02-23
2
1,293 Views
Last Modified: 2012-05-11
I'm trying to build a function, in sql server, in order to get the numeric component of any string.

I would like to check, in the first place, if the function exists. If not: create it

running the attached code I get an error regarding the keyword Function

what I'm doing wrong?
SQL = "IF NOT EXISTS ( " & _
      "SELECT  * " & _
      "From INFORMATION_SCHEMA.ROUTINES " & _
      "WHERE ROUTINE_NAME    = 'Transnumero' " & _
      "AND ROUTINE_SCHEMA  = 'dbo' " & _
      "AND ROUTINE_TYPE    = 'FUNCTION' ) " & _
      "BEGIN " & _
      "  CREATE FUNCTION dbo.Transnumero (@string VARCHAR(8000)) " & _
      "  RETURNS VARCHAR(8000) AS BEGIN " & _
      "  DECLARE @IncorrectCharLoc SMALLINT " & _
      "  SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string) " & _
      "  WHILE @IncorrectCharLoc > 0  BEGIN " & _
      "  SET @string = STUFF(@string, @IncorrectCharLoc, 1, '') " & _
      "  SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string) " & _
      "  End " & _
      "  SET @string = @string " & _
      "  RETURN @string " & _
      "  End " & _
      "END "
cn.Execute SQL

Open in new window

0
Comment
Question by:Pakhu1
2 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 34967441
Try something like this :

SQL = "IF OBJECT_ID (N'dbo.Transnumero', N'FN') IS NOT NULL" & _ 
      "DROP FUNCTION dbo.Transnumero;" & _ 
      "GO" & _ 
      "  CREATE FUNCTION dbo.Transnumero (@string VARCHAR(8000)) " & _ 
      "  RETURNS VARCHAR(8000) AS BEGIN " & _ 
      "  DECLARE @IncorrectCharLoc SMALLINT " & _ 
      "  SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string) " & _ 
      "  WHILE @IncorrectCharLoc > 0  BEGIN " & _ 
      "  SET @string = STUFF(@string, @IncorrectCharLoc, 1, '') " & _ 
      "  SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string) " & _ 
      "  End " & _ 
      "  SET @string = @string " & _ 
      "  RETURN @string " & _ 
      "  End " 
cn.Execute SQL 

Open in new window

0
 

Author Closing Comment

by:Pakhu1
ID: 35009023
I was loooking for a way not to drop the function every time I work with the database

I was hoping to find something like:

Check if function exists. If not then Exec { build function}
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

19 Experts available now in Live!

Get 1:1 Help Now