Calling UDF without dbo.function
-
Hi,
I was wondering if there is any way to define a UDF as public or independant of the database in order to call
select udf1(a,b) from table1 instead of (without the "dbo.")
select dbo.udf1(a,b) from table1
Thanks in advance
-
From sql2000 books online :
Calling User-Defined Functions
When calling a scalar user-defined function, you must supply at least a two-part name:
SELECT *, MyUser.MyScalarFunction()FROM MyTableTable-valued functions can be called by using a one-part name:
SELECT *FROM MyTableFunction()However, when you call SQL Server built-in functions that return a table, you must add the prefix :: to the name of the function:
SELECT * FROM ::fn_helpcollations()That says it all
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
-
You can create a scalar user-defined function that you can call without the two part naming, see:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1522
The trick here is the location (master DB) and owner (system_function_schema). The main drawback is that your function must be generic, i.e. not rely on any database specifics as in the example of this script.
Andy
-
Thanks Andy, your post is just awesome.
Based on this code I defined a function concat (to concat 2 strings) with the idea to supply this functionality to SQL Server compared with other databases like Oracle, MySQL, etc., in order to have more standad calls in my code and avoid to use the "+" concatenator and a "if" like
if database <> 'SQL SERVER' then
select concat(lastname,firstname) as fullname
else
select lastname+firstname as fullname
endif
I have a last question, if there is possible to define a function without 'fn_' (like the regular string functions), I was trying to do that but if I remove that prefix I get an error "'concat' is not a recognized function name."
Thanks for your support.
Here is the code
*********************
USE master
GO
-- Drop fn_DateTimeToISODate function, system or not
IF EXISTS(SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=N'FUNCTION' AND ROUTINE_SCHEMA=N'system_function_schema'
AND ROUTINE_NAME = N'fn_Concat')
BEGIN
PRINT 'Drop function owned by system_function_schema'
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
DROP FUNCTION system_function_schema.fn_Concat
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
PRINT ''
END
ELSE
IF EXISTS(SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=N'FUNCTION' AND ROUTINE_SCHEMA=N'dbo'
AND ROUTINE_NAME = N'fn_Concat')
BEGIN
PRINT 'Drop function owned by dbo'
DROP FUNCTION dbo.fn_Concat
PRINT ''
END
GO
CREATE FUNCTION fn_Concat
(
@par1 varchar(2000), @par2 varchar(2000)
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @varConcat varchar(4000)
select @varConcat = @par1+@par2
RETURN(@varConcat)
END
GO
-- This will make the a scalar user-defined function a system function
-- or the two-part name invoking rule no longer is required
PRINT 'Change owner'
EXEC sp_changeobjectowner 'fn_Concat', 'system_function_schema'
GO
PRINT 'Grant rights'
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GRANT EXEC ON system_function_schema.fn_Concat TO public AS system_function_schema
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply