SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Functions – Day 26 of 35

Answer simple quiz at the end of the blog post and -

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

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 SQLProgrammingChapter5.1Setup.sql script from 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?

  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.

Rules:

Please leave your answer in 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 India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

75 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Functions – Day 26 of 35

  1. Correct Answer : Option 3 & 4

    We need two functions to get number of hours each user has logged for: 1) the current day, and 2) month to date.

    Function 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.

    Function for 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.

    Chennai, Tamilnadu, India

    Like

  2. 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.

    3) should gives hours for day
    4) should give hours for the month

    Leo Pius
    USA

    Like

  3. Hi,

    Option 3 will work for Current Day and Option 4 will work for Month to Date. 2 functions are required to achieve this.

    Option 1 and 2 are incorrect because they both return list of values and we are expected to create a function that return scalar value.

    Thanks

    Sudhir Chawla
    New Delhi, India

    Like

  4. The correct answers 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.

    because we want to create this function as a scalar function

    I am from USA

    Like

  5. Question 26
    ANS :
    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.

    Chennai ,INDIA

    Like

  6. Correct answers are 3 and 4, because we need to return scalar values (one representing the number of hours each user has logged for the day, and the other one representing the number of hours each user has logged for month to date), and the other options return a list of values.
    Rene Castro
    El Salvador

    Like

  7. The correct answer options are 3 & 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.

    Nikhildas
    cochin
    INDIA

    Like

  8. Answer: Option 3 and Optin 4

    Explanation:

    1)we’ll create a function eg: GetCurrentDayLoginHrs(@UserId int) this will returns the number of hours the users has been logged in current day as a scalar result.

    2)again we need to create a function eg:GetLoginHrsCurrentMonth(@UserId int) which in turns returns the total logging hours of the users for the current month.

    inside this we need to perform SUM(logging hours) for userid and where month would be current month.

    Vishal Patwardhan
    Indore(India)

    Like

  9. Correct Answer : 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.

    City: Baroda

    Country: India

    Thanks
    GurjitSingh

    Like

  10. As per the question, the functions should take user ID as parameter and return number of hours for 1) Current Date 2) Current Month.

    Hence, we have to follow this steps.

    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.

    Thus, Option 3 & 4 are the correct answers.

    Ishan Shah,
    Gandhinagar,
    India

    Like

  11. 3 & 4 are correct

    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.

    Arjun,
    India

    Like

  12. The correct answers 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.

    Country of residence: India

    Like

  13. I will do:
    a) Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
    b) Create a function that returns a number value representing the number of hours that a user has logged for the current month.”
    Option.
    That means Option 3 and 4 are Correct Answers.

    Thanks…
    Rajneesh Verma
    (INDIA)

    Like

  14. Ans : 3 & 4

    To find how many hours the user logged on in the current day we have create this function
    3.Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.

    And for total hours of the current month up to current date we have create this function
    4.Create a function that returns a number value representing the number of hours that a user has logged for the current month.

    Partha,
    India

    Like

  15. Hi,

    As the requirement is 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.

    The answers would be : 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.

    Name: Hema Chaudhry
    Country : India

    Thanks,
    Hema Chaudhry

    Like

  16. 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

    Like

  17. 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

    Like

  18. 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

    Like

  19. 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)

    Like

  20. 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

    Like

  21. 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

    Like

  22. 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

    Like

  23. 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)

    Like

  24. 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

    Like

  25. 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

    Like

  26. 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.

    Like

  27. 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

    Like

  28. 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)

    Like

  29. 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

    Like

  30. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  31. 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

    Like

  32. 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

    Like

  33. 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

    Like

  34. 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

    Like

  35. 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

    Like

  36. 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

    Like

  37. 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

    Like

  38. 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

    Like

  39. 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

    Like

  40. 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

    Like

  41. 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

    Like

  42. 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).

    Like

  43. 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

    Like

  44. 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

    Like

  45. The correct options 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.

    (Sale, Nigeria)

    Like

  46. Hi Pinal,

    Challenge:
    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.

    Correct Answer:
    The correct choices are #3 and #4.

    Explanation:
    Query #1 is incorrect. It will only return the login time for each user ID passed to it. The login time is only part of the solution (for a given login session). You also need to time the user logged off, then find the difference between the two values.
    Query #2 will provide a list of users who have logged more hours than the current user. While this result set may be useful, it doesn’t meet our requirements.
    Query #3 is the query we need to return the number of hours logged by the user ID for the current day.
    Query #4 is the query we need to return the number of hours logged by the user from the beginning of the month through today.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Like

  47. The correct answer is:
    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.

    Sudeepta,
    India

    Like

  48. Pingback: SQL SERVER – Programming and Development – Book Available for SQL Server Certification Journey to SQLAuthority

  49. Pingback: SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5 « SQL Server Journey with SQL Authority

  50. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s