• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1408
  • Last Modified:

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

0
Pakhu1
Asked:
Pakhu1
1 Solution
 
Paul JacksonSoftware EngineerCommented:
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
 
Pakhu1Author Commented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now