February 25, 2011 at 10:05 am
Ive created a user defined function
dbo.UDF_ParseNumChars
when I try to use it in a select statement, its not being recognised
'UDF_ParseNumChars' is not a recognized built-in function name.
im trying to execute it from the management studio, so it shouldnt be a permission problem (should it?)
anyone know what im doing wrong here ?
February 25, 2011 at 10:32 am
Sorry, but the force is weak. Can't see what you see.
Please post the DDL for you function and the code you are trying to execute using the function.
February 25, 2011 at 10:33 am
Yes, you just need to prefix it with "dbo." then it will be recognised.
February 25, 2011 at 11:29 am
indeed, a udf needs to be addressed schema qualified.
select yourschema.yourudf( paramcol )
from yourobject
....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 26, 2011 at 5:39 am
if you read my post again, youll see that it was prefixed with dbo.
February 26, 2011 at 6:07 am
what is your select statement then?
February 26, 2011 at 11:00 am
is your connection positioned in the correct database ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 26, 2011 at 6:05 pm
mwwheeler (2/26/2011)
if you read my post again, youll see that it was prefixed with dbo.
Second request, please post the DDL for your function plus the code calling your function that gets there error.
Can't help you based solely on your current posts. There simply isn't enough information to give you a anything except wild guesses.
February 27, 2011 at 1:48 am
mwwheeler (2/26/2011)
if you read my post again, youll see that it was prefixed with dbo.
-- Scalar function
CREATE FUNCTION dbo.F() RETURNS INT AS BEGIN DECLARE @i INT = 1; RETURN @i END;
GO
SELECT dbo.F();
-- Error - not a table-valued function
SELECT * FROM dbo.F()
GO
-- Multi-statement table-valued function
CREATE FUNCTION dbo.F2() RETURNS @T TABLE (i INT) AS BEGIN INSERT @T VALUES (1); RETURN; END;
GO
SELECT * FROM dbo.F2();
SELECT * FROM (VALUES(1)) V(v) CROSS APPLY dbo.F2();
-- Error - returns a table, not a scalar
SELECT dbo.F2();
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 10:31 am
mwwheeler (2/25/2011)
Ive created a user defined functiondbo.UDF_ParseNumChars
when I try to use it in a select statement, its not being recognised
'UDF_ParseNumChars' is not a recognized built-in function name.
im trying to execute it from the management studio, so it shouldnt be a permission problem (should it?)
anyone know what im doing wrong here ?
As Paul demonstrated, it depends on the type of function you've created as to where you can use it.
Also, based on the name of the function, it would appear to be a "splitter". Most folks end up writting splitters that are, ummmm.... performance challenged. If you post your function, perhaps we could make some recommendations which may improve performance and decrease the number of resources it uses.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2011 at 2:25 am
heres the code
ALTER FUNCTION [dbo].[UDF_ParseNumChars]
(
@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
IF LEN(@string) = 0
SET @string = '0'
ELSE
SET @string = @string
RETURN @string
END
Im working on an existing database and the original designers, in their wisdom, decided to mix and match a particular columns contents, sometime it contains a numeric value, othertimes a text value (the underlying datatype is varchar)
one of my queries does a date calculation using a 'numeric' value in this column, it converts it to an integer and does a bit of math with it. I basically wanted to use this function to make sure the contents can be safely converted to an integer and not cause an exception (if it contained a word). Is there a more efficient/safer way to do this ?
February 28, 2011 at 5:54 am
Okay, since I can't seem to get everything I have asked for, here is my wild guess. You are in the same database that has the function.
If you reread my two requests, I asked for the DDL for the function (provided) and the the code you are using to call the function (still not provided).
Looking at the code for the function, Jeff is correct that this can be rewritten to be more scalable and efficient.
February 28, 2011 at 6:09 am
and (GetDate() <= (DATEADD( month,CONVERT(INT,Lookup.SubRef),Data_Landline.ModifiedDate)))
i was trying to use this
and (GetDate() <= (DATEADD( month,CONVERT(INT,dbo.UDF_ParseNumChars(Lookup.SubRef)),Data_Landline.ModifiedDate)))
the subref column may contain some text instead of a number, so the calculation wont be valid in that instance
February 28, 2011 at 6:34 am
mwwheeler (2/28/2011)
Im working on an existing database and the original designers, in their wisdom, decided to mix and match a particular columns contents, sometime it contains a numeric value, othertimes a text value (the underlying datatype is varchar)one of my queries does a date calculation using a 'numeric' value in this column, it converts it to an integer and does a bit of math with it. I basically wanted to use this function to make sure the contents can be safely converted to an integer and not cause an exception (if it contained a word). Is there a more efficient/safer way to do this ?
Wouldn't it be easier just not to touch any rows where the data contained a non-numeric? The function strips all the character data out - turning '80FF96' into '8096' for example. It is unlikely that converting the resulting value to a date would yield anything useful. Is there an extra consideration we should be aware of? I would likely approach this problem using a variation of this idea:
CREATE TABLE dbo.MixedUp
(
pk INT IDENTITY PRIMARY KEY,
data VARCHAR(50),
padding CHAR(1000) NOT NULL DEFAULT('')
)
;
INSERT dbo.MixedUp
(data)
VALUES ('40601'), ('40600'), ('8000F'),
('ZZZXY'), ('40412'), ('Apple')
;
-- Could be persisted
ALTER TABLE dbo.MixedUp
ADD data_first_non_numeric
AS PATINDEX('%[^0-9]%', data)
;
-- Can add to an index without persisting
CREATE INDEX [IX dbo.MixedUp data_first_non_numeric (data)]
ON dbo.MixedUp
(
data_first_non_numeric
)
INCLUDE (
data
);
;
-- Seek
SELECT DATEADD(DAY, CONVERT(INT, MU.data), 0)
FROM dbo.MixedUp AS MU
WHERE MU.data_first_non_numeric = 0
;
DROP TABLE dbo.MixedUp
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 28, 2011 at 11:34 pm
mwwheeler (2/28/2011)
and (GetDate() <= (DATEADD( month,CONVERT(INT,Lookup.SubRef),Data_Landline.ModifiedDate)))i was trying to use this
and (GetDate() <= (DATEADD( month,CONVERT(INT,dbo.UDF_ParseNumChars(Lookup.SubRef)),Data_Landline.ModifiedDate)))
the subref column may contain some text instead of a number, so the calculation wont be valid in that instance
Code snippet, getting closer but still not there.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply