"dbo." before user defined functions

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

General SQL Server Forums

New to SQL Server Programming

"dbo." before user defined functions

Author

Topic

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-16 : 10:41:39

Hello

When I use a user defined function in a query, I should add "dbo." before the function name. Is there a way to write a valid sql query without this prefix before the user defined functions?

Regards,

mathmax

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 10:48:33

Only scalar valued functions need dbo prefix.
Table valued functin do not.

The reason for this? Have no idea.

E 12°55'05.25"
N 56°04'39.16"

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-16 : 10:57:48

I've executed a script that generate the function. It generated it as a Scalar Valued function. How to create it as a Table Valued function ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 10:59:44

quote:
Originally posted by mathmax

I've executed a script that generate the function. It generated it as a Scalar Valued function. How to create it as a Table Valued function ?

just modify it to return a table as result.

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-16 : 11:09:58

Is there a difference of performance between table valued function and scalar valued functions? Or any other difference when using them?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 11:30:46

They are completely different. One returns a table, the other returns a single value. Write the function that returns what you need. Don't completely change your code just to avoid typing 4 characters before a function name!

- Jeff
http://weblogs.sqlteam.com/JeffS

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-19 : 18:40:33

Is it possible to create scalar user defined function in an schema than .dbo ? How ?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-19 : 18:45:57

Yes. Just specify the name of the schema when you create it.
You do not need to specify "dbo" when calling the function. You need to specify the name of the schema to which it belongs, which is "dbo" by default.

e4 d5 xd5 Nf6

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-19 : 18:52:19

Thank you. And for functions created in Visual Studio ? Is there a way to deploy it in a custom schema ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-19 : 19:34:47

You can specify schema when create function, ensure you have proper permission in that schema.

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-19 : 19:37:57

Where can I specify the schema?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-19 : 21:16:04

When you enter function name.

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-19 : 21:39:47

I mean in Visual Studio. Is it possible to specify a schema for .net function deployed to SQL Server ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-19 : 22:17:35

Don't know how you do that in .net, type schema.function_name when you create function in sql. Like:

CREATE FUNCTION schema.function_name ...

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-20 : 09:18:26

Does anybody knows how to specify a schema for .net function deployed to SQL Server ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 13:06:49

CREATE SCHEMA ThisIsMySchemaName

E 12°55'05.25"
N 56°04'39.16"

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-20 : 14:54:54

Yes this is a script for create a schema.
I want to specify inside .NET code in which schema the function should be added.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-20 : 14:57:29

As has already been mentioned, can't you just do dbo.FunctionName in .NET?

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

Subscribe to my blog

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-20 : 19:04:29

You mean :
[SqlFunction]
public static SqlBoolean dbo.FunctionName()
{
return true;
}

This is not valid in c#. This will not compile. There should be an other place to specify in which schema the function should be deployed, but I cannot find...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 19:17:56

The Function name in the CLR is what it is, because the function has no access to the schema.
It's when you assign the CLR to a specific database, you can set the schema.

Why? Because you should be able to deploy the CLR to another database and use under another schema.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 19:25:50

This is how you name the external function.

First, create an assembly similar to this

CREATE ASSEMBLY DiskSpace 
FROM 'C:\SQLTools\DiskSpace.dll'
WITH PERMISSION_SET = UNSAFE
GO

This is just for attaching the external function library to SQL Server.

Now write like this

CREATE PROC dbo.isp_DiskSpace @serverName nvarchar(4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME DiskSpace.StoredProcedures.isp_DiskSpace
GO

to name the external function to be used internally in SQL Server.
You can change the part in red to change schema of function.

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources