472,107 Members | 1,340 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,107 software developers and data experts.

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 3559
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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.