Pakhu1
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was hoping to find something like:
Check if function exists. If not then Exec { build function}