Let us learn about table valued functions. Every day one winner from the United States will get Joes 2 Pros Volume 4.
Table-Valued Functions
Scalar-valued functions return a single value. Table-valued functions return tabular result sets (“tabular” meaning like a table). Table-valued functions look a lot like views because they both show us a tabular result set based on an underlying query. Table-valued functions can be based on one or more base tables.
Creating and Implementing Table-Valued Functions
The body of a table-valued function will essentially contain a query. Let’s begin with a query containing four fields and all of the records from the CurrentProducts table.
This query will become the heart of a new table-valued function, GetAllProducts. By placing the query within the set of parentheses after the keyword RETURN, we have the body of the function. The RETURNS TABLE keyword specifies that the table-valued function GetAllProducts must return the result in the form of a table.
CREATE FUNCTION GetAllProducts( ) RETURNS TABLE AS RETURN (SELECT ProductID, ProductName, RetailPrice, Category FROM CurrentProducts) GO
Just how do you query a table-valued function? The syntax is somewhat similar to how you would run a SELECT statement against a table or a view. All functions need to be called by using a set of parentheses with all required parameters inside them. If the function has no parameters (which is currently the case with GetAllProducts), then you will simply include an empty set of parentheses.
To view all of the table-valued functions contained in the JProCo database from within the Object Explorer tree, traverse this path:
OE > Databases > JProCo > Programmability > Functions > Table-valued Functions
Views versus Parameterized Table-Valued Functions
Views and table-valued functions are both useful ways to see the result set for a pre-defined query. There is no way to pass a variable into a view and change the way it runs. Views are hard-coded and their criterion does not change. A table-valued function can display different results by passing values into its parameter(s) at runtime. Let’s begin by selecting all ‘No-Stay’ records from the CurrentProducts table. We want to turn this query into a function and allow that function to pick the category.
We’re going to enclose our query in parentheses, indent it, and then add some code to create a function. We will create the GetCategoryProducts function which takes a @Category parameter. The query within the table-valued function will predicate on the value passed in when the function is called.
Change the parameter value to ‘Medium-stay’ and run this query. The GetCategoryProducts function now returns164 records.
SELECT * FROM GetCategoryProducts('Medium-stay')
Whenever you call a function, you must remember to use a set of parentheses and include the parameter(s) which the function expects. Let’s demonstrate the error message which results from forgetting to include the needed parameter within the parentheses. SQL Server’s error message tells us that our code doesn’t match the function’s parameter signature. In other words, it reminds us that we need to specify a category.
SELECT * FROM GetCategoryProducts()
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function GetCategoryProducts.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter8.1Setup.sql script from Volume 4.
Question 26
You need to create two functions that will each return a scalar result of the number of hours each user has logged for: 1) the current day, and 2) month to date. You will pass in the user ID as a parameter value. What two things must you do?
- Create a function that returns a list of values representing the login times for a given user.
- Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.
- Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
- Create a function that returns a number value representing the number of hours that a user has logged for the current month.
I hope yo have enjoyed this blog post about table valued functions.
Rules:
Please leave your answer in the comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from the United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
73 Comments. Leave new
Correct Answer is Option 3 & 4.
3.Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4.Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Country:India
Thanks ,
Fazal Vahora
The Correct Answers are:
3.Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4.Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Explanation:
userID should be sent in as the Parameter to both the Functions.
Both the function will return a single value ie. scalar result.
(No.3) – for the current day
(No.4) – for the current month
Country:
India
The answer is option 3 and 4.
Since the question is to find out the number of hours the user has logged for current day and month to date.
We need to
Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Raj
USA
The two things we must do for this question are
Option 3 and 4
3.Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4.Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Explanation: In this question we have to create two functions that returns scalar result and option 1 and 2 returns the list or tabular result. And option 3 and 4 returns single value as asked in the question.
Country – INDIA (Gujarat)
The Correct Answers for this question is Option -3 &4
Thanks,
Narendra(India)
Hi Sir,
3 and 4 are the correct answers.
According to question we need to return a scalar result of the number of hours each user has logged in
– Current day : for this the correct option is 3 that is
Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
This function will return the no of hours the user has logged in for the current day.
– month to date : for this the correct option is 4 that is
Create a function that returns a number value representing the number of hours that a user has logged for the current month.
This function will return the no of hours the user has logged in for the current month till date.
Regards,
P.Anish Shenoy
INDIA
The correct answers are:
Option 3:Option 3 is correct as the query has to return a scalar value(means single value) indicating the number of hours the user has logged for the current day. The query takes the user ID value for executing the query to calculate number of hours for a specific user, which is passed as a parameter to the function. Thus it gives hours for the current day.
Option 4:Option 4 is correct as the query has to return a scalar value(means single value) indicating the number of hours the user has logged for the current monthtodate. The query takes the user ID value for executing the query to calculate number of hours in the current monthtodate for a specific user, which is passed as a parameter to the function. Thus it gives hours for the current month to date.
Options 1 and 2 are not correct as the function returns a list of values.
Country of Residence: Andhra pradesh, India
Correct option is 3 & 4.
3.Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4.Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Karan,
India
The correct answer is option 3 & 4
3.Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4.Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Thanks,
Prasad Yangamuni
INDIA (PUNE)
The Correct options are 3 & 4:
3. Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4. Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Option 3 will return a scalar result of the number of hours each user has logged for the current day, and option 4 will return a scalar result of the current month
Regards,
Geetika Bhambri
Ahmedabad(Gujarat)-INDIA
Correct options : 3 & 4
3. Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4. Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Explanation: As we need to get “a scalar result of the number of hours each user has logged for: 1) the current day, and 2) month to date.”, only the above options return scalar values representing the number of hours. While the other two options returns a list of values, which we don’t need to get.
Country of resident: India
Correct Answer is # 3 & 4.
3. Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4. Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Because we need to return scalar values one for the current day and other for current month.
userID should be sent in as the Parameter to both the Functions. Both the function will return a single value ie. scalar result.
Answer # 3 – For the current day
Answer # 4 – For the current month
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
The correct answers are option 3 & 4.
Thanks,
Alpesh Gorasia
INDIA
Answer is option 3 and 4
Regards
Rajesh
From india
Thanks Pinal,
Correct Answer:
For Current Day :
3)Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
For Current Month:
4) Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Krishan kumar mishra
India
ANS: 3 And 4.
Explanation
=========
For Current day
Option : 3
Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
For month to date
Option : 4
Create a function that returns a number value representing the number of hours that a user has logged for the current month.
Option 1 and 2 are incorrect because both return list of values not scalar value.
Thanks,
Mitesh Modi
(India)
Correct answer should be 3 & 4.
New Delhi
India
Answers 3 and 4
3.Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4.Create a function that returns a number value representing the number of hours that
Country : India
Qn 26) Correct option is : 3 and 4
— G.VenkateshPrabu || Bangalore || India
Correct answer is option 3) & 4)
option 1) & 2) are clearly invalid as it is specified in the requirement that each function should return scalar values.Means each function should return single value.
Sumit
India