469,085 Members | 1,039 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,085 developers. It's quick & easy.

call user defined sql function within sql statement

120 100+
hi guys

I have a user defined sql function in my sql server db called TenPercentDiscount but not sure how to call it within sql statement?

I'm ideally looking for my function to be applied to a select * sql statement similar to my amateur attempt below

iv tried both
"SELECT * FROM TenPercentDiscount(RMT_2DayTours) WHERE ID IN (1,2,3)"
"SELECT TenPercentDiscount, * FROM RMT_2DayTours WHERE ID IN (1,2,3)"

but not having any joy? my function code is
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION TenPercentDiscount 
  2. (@InputVals int)
  3. RETURNS int
  4. AS
  5. BEGIN
  6. declare @convertedVal varchar(30)
  7. set @convertedVal =  Cast(@InputVals  * 0.9 as int)
  8. return  @convertedVal 
  9. end
please advise
Nov 11 '10 #1
4 3443
code green
1,726 Expert 1GB
If RMT_2DayTours is a table then
Expand|Select|Wrap|Line Numbers
  1. SELECT TenPercentDiscount(field_from_RMT_2DayTours) As discounted
  2. FROM RMT_2DayTours 
  3. WHERE ID IN (1,2,3)"
Nov 11 '10 #2
120 100+
hi code green - yes RMT_2DayTours is a table. Is it bad practice to not include dbo. before a table name? if it is please let me know as I dont want to continue with bad habits.

I've tried your suggestion
Expand|Select|Wrap|Line Numbers
  1. SELECT TenPercentDiscount(Double_Redleaf_Price) as Double_Redleaf_Price 
  2. FROM RMT_2DayTours 
  3. WHERE ID IN (1,2,3)"
but im getting the following error
Microsoft OLE DB Provider for SQL Server error '80040e14'

'TenPercentDiscount' is not a recognized built-in function name.

please advise
Nov 11 '10 #3
2,878 Expert 2GB
The "dbo" prefix means the object you are calling is owned by dbo. If you have a lot of object (tables, SP, function) in your database that's owned by different users, they may or may not have the same name. To make sure you are calling, reading or executing the right object, the owner name is usually included. As rule of thumb, yes, you should use the owner.

In calling the function, SQL Server, more often than not, expects the owner's name. So do a dbo.TenPercentDiscount(Double_Redleaf_Price). To be very sure, put the database name in front.

Happy Coding!!!

~~ CK
Nov 11 '10 #4
code green
1,726 Expert 1GB
Sorry about the missing dbo.
I should know better because my SQL Server will throw an error without the dbo
(similar to yours, strangely)
Nov 12 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mphoza | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.