can't access function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

can't access function

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-31 : 22:07:17

what am i doing wrong

I created a function

CREATE FUNCTION cleanurl
(@urltoclean nvarchar(50))

RETURNS nvarchar(50) AS  
BEGIN 
declare @cleanurl nvarchar(50)
select @cleanurl=replace(replace(@urltoclean,' ','-'),'#','')
return @cleanurl
END

which executed successfully

but when i do
select * from products where cleanurl(title) = 'this is a test'

i get
'cleanurl' is not a recognized function name.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-01 : 07:03:35

For UDF's you have to explicitly specify the schema name even if it is dbo.

select * from products where dbo.cleanurl(title) = 'this is a test'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-01 : 09:05:17

Also, you dont need a function. You can directly use

select * from products where replace(replace(title,' ','-'),'#','')
= 'this is a test'

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-04-02 : 13:02:34

i see if i use the username it works though I will have to change in the code if i move servers (which I may do )

i want it in a function as I want to use it in a nubmber of places and be able to add more replaces to the function without having to change everywhere

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-02 : 14:27:36

You will not need to change the code if you specify dbo as the username. Use dbo and make it simple.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources