Solved

Create function if not exists in Sql Server

Posted on 2011-02-23
2
1,296 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dataset not reading table data 12 46
SQL Server; storing data in offline mode. 10 67
Analysis of table use 7 42
display data in text field from data base for updating 6 50
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

23 Experts available now in Live!

Get 1:1 Help Now