call user defined sql function within sql statement

[omar999]

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

CREATE FUNCTION TenPercentDiscount 
(@InputVals int)
RETURNS int
AS
BEGIN
declare @convertedVal varchar(30)
set @convertedVal =  Cast(@InputVals  * 0.9 as int)
return  @convertedVal 
end

please advise
omar

[code green]

If RMT_2DayTours is a table then

SELECT TenPercentDiscount(field_from_RMT_2DayTours) As discounted
FROM RMT_2DayTours 
WHERE ID IN (1,2,3)"

[omar999]

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

SELECT TenPercentDiscount(Double_Redleaf_Price) as Double_Redleaf_Price 
FROM RMT_2DayTours 
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

[ck9663]

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

[code green]

Sorry about the missing dbo.
I should know better because my SQL Server will throw an error without the dbo
(similar to yours, strangely)