SQL SERVER – Table Valued Functions – Day 26 of 35

Let us learn about table valued functions. Every day one winner from the United States will get Joes 2 Pros Volume 4.

SQL SERVER - Table Valued Functions - Day 26 of 35 joes2pros4

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.

SQL SERVER - Table Valued Functions - Day 26 of 35 j2p_26_1

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.

SQL SERVER - Table Valued Functions - Day 26 of 35 j2p_26_2

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

SQL SERVER - Table Valued Functions - Day 26 of 35 j2p_26_3

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.

SQL SERVER - Table Valued Functions - Day 26 of 35 j2p_26_4

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.

SQL SERVER - Table Valued Functions - Day 26 of 35 j2p_26_5

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?

  1. Create a function that returns a list of values representing the login times for a given user.
  2. Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.
  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.

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)

, , ,
Previous Post
SQL SERVER – Author’s Book is Available in India and USA
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – SQL Server Error Messages – Day 27 of 35

Related Posts

73 Comments. Leave new

  • Mike Michalicek
    August 26, 2011 5:32 pm

    Options 3 & 4

    USA

    Mike Michalicek

    Reply
  • Options 3 and 4 will be required to get the desired results.

    Country: United States

    Reply
  • 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.

    Somnath Desai

    India

    Reply
  • The answer would be #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.

    These are the only ones that satisfy the criteria given. You asked for the number of hours logged in in a current day or month, not the login times or people who have logged in more.

    Deb
    USA

    Reply
  • Nikhil Mahajan
    August 26, 2011 6:09 pm

    the correct answer is option 3 and 4 i.e.

    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.

    India

    Reply
  • Answer: Option 3 & 4
    Option 1 & 2 are incorrect since the question asked for scalar values to be returned and those options return multiple values. Only 3 and 4 return scalar values for the hours worked.

    Country: USA

    Reply
  • Raushan Kumar Jha
    August 26, 2011 6:29 pm

    Ans : 3 & 4

    to get the number of hours each user has logged the 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.

    to get the number of hours each user has logged month to date :-
    4. Create a function that returns a number value representing the number of hours that a user has logged for the current month.

    —- India

    Reply
  • The correct options are #3 and #4

    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.

    options 1 and two both return lists and per the question we were to generate a scalar value.

    Country of Residence: USA

    Reply
  • correct answers are 3 and 4

    i’m from india

    Reply
  • 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.

    Chetan – USA

    Reply
  • Diljeet Kumari
    August 26, 2011 7:31 pm

    Question 26) answers are 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.

    Diljeet Kumari
    country : India

    Reply
  • The answer is option 3 & 4, need 2 functions to meet the requirements stated in the question.

    Reply
  • The answer is option 3 & 4, need 2 functions to meet the requirements stated in the question.

    Ramdas
    NC,USA

    Reply
  • 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

    Reply
  • 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.

    Gordon Kane
    Allen TX
    USA

    Reply
  • Answer: Option 3 & 5

    Query Here:

    CREATE TABLE USER_LOGIN_HISTORY
    (
    USER_ID INT,
    USER_LOGIN_TIME DATETIME,
    USER_LOGOUT_TIME DATETIME
    )

    INSERT INTO USER_LOGIN_HISTORY VALUES(1, ‘8/25/2011 11:00’, ‘8/25/2011 12:00’)
    INSERT INTO USER_LOGIN_HISTORY VALUES(1, ‘8/25/2011 13:00’, ‘8/25/2011 18:00’)

    INSERT INTO USER_LOGIN_HISTORY VALUES(1, ‘8/26/2011 11:00’, ‘8/26/2011 12:00’)
    INSERT INTO USER_LOGIN_HISTORY VALUES(1, ‘8/26/2011 13:00’, ‘8/26/2011 18:00’)

    SELECT * FROM USER_LOGIN_HISTORY

    CREATE FUNCTION [func_GET_NUMBER_OF_HOURS_TODAY_BY_USER](@USER_ID INT)
    RETURNS INT
    BEGIN
    DECLARE @RESULT INT
    SELECT @RESULT = SUM(DATEDIFF(HH, USER_LOGIN_TIME, USER_LOGOUT_TIME)) FROM USER_LOGIN_HISTORY
    WHERE
    [USER_ID] = @USER_ID
    AND DATEDIFF(D, GETDATE(), USER_LOGIN_TIME) = 0
    RETURN @RESULT
    END

    SELECT dbo.func_GET_NUMBER_OF_HOURS_TODAY_BY_USER(1) AS NUMBER_OF_HOURS_TODAY

    CREATE FUNCTION [func_GET_NUMBER_OF_HOURS_CURRENT_MONTH_BY_USER](@USER_ID INT)
    RETURNS INT
    BEGIN
    DECLARE @RESULT INT
    SELECT @RESULT = SUM(DATEDIFF(HH, USER_LOGIN_TIME, USER_LOGOUT_TIME)) FROM USER_LOGIN_HISTORY
    WHERE
    [USER_ID] = @USER_ID
    AND DATEDIFF(M, GETDATE(), USER_LOGIN_TIME) = 0
    RETURN @RESULT
    END

    SELECT dbo.func_GET_NUMBER_OF_HOURS_CURRENT_MONTH_BY_USER(1) AS NUMBER_OF_HOURS_CURRENT_MONTH

    -Devarajan (India).

    Reply
  • Answers 3 and 4

    Sreeram
    India

    Reply
  • Basavaraj Biradar
    August 26, 2011 11:36 pm

    Correct Answers is: Option 3 and 4

    Thanks,
    Basavaraj
    India

    Reply
  • dilipkumarjena
    August 27, 2011 12:03 am

    answer for the Question 26 are #3 and #4.

    Explanation:
    Asked : We need to create two functions that will each return a scalar result of the number of hours each user has logged for:
    A)the current day and
    B) month to date

    Given : You will pass in the user ID as a parameter value

    Option 3)Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
    A) this is perfect for the for Current Day

    Option 4)Create a function that returns a number value representing the number of hours that a user has logged for the current month.
    A) This works for the Month to Date.

    Why other are wrong:

    Option 1)Create a function that returns a list of values representing the login times for a given user.
    A) We need a scalar value hence invalid

    Option 2)Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.
    A) We need a scalar value hence invalid

    Hence clearly option 3 and 4 are correct.

    Dilip Kumar Jena
    Country : INDIA

    Reply
  • vinay (@vinayprasadv)
    August 27, 2011 12:33 am

    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.

    Vinay,
    Pune, INDIA

    Reply

Leave a Reply