Solved

Create function if not exists in Sql Server

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Date from a string 4 65
Parsing the XML data to SQL Server 4 66
Upgrading SQL 2005 Express to 2008 R2 Express 31 94
Grid querry results 41 78
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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