Link to home
Start Free TrialLog in
Avatar of Pakhu1
Pakhu1Flag for Afghanistan

asked on

Create function if not exists in Sql Server

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

ASKER CERTIFIED SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Pakhu1

ASKER

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}