userdefined function without prefix dbo.function

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 2008 Forums

Transact-SQL (2008)

userdefined function without prefix dbo.function

Author

Topic

MMoerkerk
Starting Member

3 Posts

Posted - 2011-05-23 : 16:52:47

Microsoft SQL 2008R2 Server Management Studio 10.50.1600.1

I want make an function in SQL like this:

CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
END

syntax

SELECT dbo.fx_SumTwoValues(1,2) AS SumOfTwoValues

Now I want to make function without the DBO. so I can use this syntax

SELECT fx_SumTwoValues(1,2) AS SumOfTwoValues

What do I have to do to remove this dbo. to call my function???

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-23 : 17:05:38

User defined functions have to be qualified with their schemas. No way around it.

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 17:08:12

Why do you want to do this?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MMoerkerk
Starting Member

3 Posts

Posted - 2011-05-23 : 17:12:24

quote:
Originally posted by GilaMonster

User defined functions have to be qualified with their schemas. No way around it.

--
Gail Shaw
SQL Server MVP

If I use schema then I don't have to use prefix dbo.??

What does schema does or what is the function of a schema.

MMoerkerk
Starting Member

3 Posts

Posted - 2011-05-23 : 17:17:03

quote:
Originally posted by nigelrivett

Why do you want to do this?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

to solve case in program what calls ifnull function instead of isnull. So I want to make ifnull function that calls system function isnull and give back the value of isnull function.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-23 : 17:59:51

quote:
Originally posted by MMoerkerk

quote:
Originally posted by GilaMonster

User defined functions have to be qualified with their schemas. No way around it.

--
Gail Shaw
SQL Server MVP

If I use schema then I don't have to use prefix dbo.??

What does schema does or what is the function of a schema.

DBO is the schema. You can use a different schema than DBO (assuming you created the fuction with another schema), but you must use a schema to envoke a UDF.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-24 : 01:30:44

quote:
Originally posted by MMoerkerk

to solve case in program what calls ifnull function instead of isnull. So I want to make ifnull function that calls system function isnull and give back the value of isnull function.

Fix the problem, not the symptoms. Fix (or get someone to fix) the application.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources