473,683 Members | 2,643 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

call user defined sql function within sql statement

120 New Member
hi guys

I have a user defined sql function in my sql server db called TenPercentDisco unt 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 TenPercentDisco unt(RMT_2DayTou rs) WHERE ID IN (1,2,3)"
"SELECT TenPercentDisco unt, * 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
omar
Nov 11 '10 #1
4 3667
code green
1,726 Recognized Expert Top Contributor
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
omar999
120 New Member
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'

'TenPercentDisc ount' is not a recognized built-in function name.

please advise
Nov 11 '10 #3
ck9663
2,878 Recognized Expert Specialist
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.TenPercentD iscount(Double_ Redleaf_Price). To be very sure, put the database name in front.

Happy Coding!!!

~~ CK
Nov 11 '10 #4
code green
1,726 Recognized Expert Top Contributor
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

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

Similar topics

1
11566
by: Joel Thornton | last post by:
Is it possible to call a user-defined function without prefixing it with 'dbo.' within a SELECT clause somehow? Just curious; it's not a big issue but just a stylistic one for me. Thanks! Joel Thornton ~ <groups@joelpt.eml.cc>
2
2559
by: Steve D | last post by:
I've looked all over but can't find a solid answer. I've got a function that runs from a View and when the function runs the first time it is calculating a Temperature for a group of Formulas. What I want to do is calculate this temperature in the UDF on the first pass and store it somewhere (table, global variable etc.). On every execution after that I'd like to use the value stored so I don't have to recalculate the value again for...
2
1675
by: Mountain Man | last post by:
Hi, I'm trying to use window.open from inside a user defined function, and it's not working. A code example is shown below. Thanks for any help you can give. Mountain Man ============ <script>
3
2538
by: Gary Besta | last post by:
I am trying to add a simple case statement to a stored procedure or user defined function. However when I try and save the function/procedure I get 2 syntax errors. Running the query in query analyser works fine and a result is given with no syntax errors. I believe its something to do with the spaces in the field names. Not my choice as its an existing system I have to work around. Any help greatly appreciated SQL Query
13
3487
by: Maroon | last post by:
Hi, I want to write a standard java user defined function, like this example:- select db_fun("roll"), roll from student; **db_fun() will work for all tables of the all databse here db_fun is the function that i want to write. The student table is:- roll name 1 'A'
2
8212
by: Mrs Howl | last post by:
I have a query that just reads one table and appends to an output table, one-for-one. No criteria. It's not a Total query (i.e. no group by). It normally run run in minutes, but gets horribly slowed down because five of my output fields are text fields and the expression that gets appended is a user-defined function I wrote which is very simple (just a few lines long). Here's the code of my function: Public Function tformat(num As...
1
2559
by: Mphoza | last post by:
Hi! Programmers,,,,,, Scenario I have a DataGrid with the delete button, If the user delete the row I'd like to show a confirmation dialog box with two buttons(Yes,No). If the user click yes the program must call my user-defined function to delete the row from database. The problem is I do not know how to create a confirm on deletes in a datagrid.
4
7660
by: Mahesh S | last post by:
Hi I want to write a user defined function which does the following. 1. Take a string value as input parameter 2. Should execute a sql statement: select col1, col2, col3, col4 from table_name where name = value 3. Do some arithmetic operations on the values returned by the above sql statement. 4. Return the above arithmetic operation as a float value
2
2626
by: Mark B | last post by:
I have been able to retrieve the scalar value of a SQL Server Stored Procedure as follows: Shared Function fGetGroupPerformanceStatistic(ByVal strGroup As String) As String Dim sqlConnection1 As New SqlConnection("Data Source=MARK\SQLEXPRESS;Initial Catalog=GroupSales;Integrated Security=True;") Dim cmd As New SqlCommand
1
5938
by: qwedster | last post by:
Hola! In the following code snippet, I am creating User Defined Function (T-SQL) programmatically into database from C# Code: BTW, this code originally I wrote for creating StoredProcedure programmatically, which worked fine. Now, I am trying to create User Defined Function programmatically (by just replacing "StoredProcedure" class with "UserDefinedFunction" class). The User Defined Function checks if "FirstName" exists in "Customer"...
0
8568
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8504
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8922
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8751
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7581
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6429
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5789
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4519
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2934
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.