User-defined functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a scalar user-defined function

Consider the following SQL user-defined function and query:

CREATE FUNCTION dbo.MultiplyByTwo(@num INT) RETURNS INT AS BEGIN RETURN @num * 2; END;
SELECT dbo.MultiplyByTwo(5) AS Result;

What is the output of the SELECT query?

SQL

CREATE FUNCTION dbo.MultiplyByTwo(@num INT) RETURNS INT AS BEGIN RETURN @num * 2; END;
SELECT dbo.MultiplyByTwo(5) AS Result;

A Result: 10

B Result: 7

C Result: 25

D Syntax error in function definition

💡 Hint

The function multiplies the input by 2.

The function takes an integer input and returns its value multiplied by 2. So 5 * 2 = 10.

📝 Syntax

intermediate

Identify the syntax error in this table-valued function

Examine the following SQL code for a table-valued function:

CREATE FUNCTION dbo.GetEvenNumbers() RETURNS TABLE AS RETURN (SELECT Number FROM Numbers WHERE Number % 2 = 0);

Which option correctly identifies the syntax error?

SQL

CREATE FUNCTION dbo.GetEvenNumbers() RETURNS TABLE AS RETURN (SELECT Number FROM Numbers WHERE Number % 2 = 0);

A Missing BEGIN and END block around the RETURN statement

B Incorrect use of RETURNS TABLE without specifying TABLE type

C No syntax error; the function is valid

D Missing parentheses after RETURNS TABLE

💡 Hint

Inline table-valued functions can omit BEGIN and END.

This is a valid inline table-valued function syntax in SQL Server. It returns a table directly from the SELECT statement.

❓ optimization

advanced

Optimizing a scalar function for performance

You have a scalar function that calculates the square of a number:

CREATE FUNCTION dbo.Square(@x INT) RETURNS INT AS BEGIN RETURN @x * @x; END;

Which option is the best way to optimize this function for performance in SQL Server?

SQL

CREATE FUNCTION dbo.Square(@x INT) RETURNS INT AS BEGIN RETURN @x * @x; END;

A Keep it as a scalar function but add WITH SCHEMABINDING

B Rewrite it as an inline scalar function using RETURNS without BEGIN...END

C Rewrite it as an inline table-valued function

D Convert it to a stored procedure

💡 Hint

Inline scalar functions can improve performance by avoiding context switches.

Using an inline scalar function (RETURNS expression without BEGIN...END) allows SQL Server to inline the function logic, improving performance.

🔧 Debug

advanced

Debugging a user-defined function with NULL handling

Given this function:

CREATE FUNCTION dbo.SafeDivide(@a INT, @b INT) RETURNS FLOAT AS BEGIN RETURN @a / @b; END;

What error or issue will occur when calling dbo.SafeDivide(10, 0)?

SQL

CREATE FUNCTION dbo.SafeDivide(@a INT, @b INT) RETURNS FLOAT AS BEGIN RETURN @a / @b; END;

A Division by zero error at runtime

B Returns NULL without error

C Syntax error in function definition

D Returns 0 without error

💡 Hint

Dividing by zero is not allowed in SQL arithmetic.

Dividing by zero causes a runtime error in SQL Server, so calling the function with 0 as denominator will fail.

🧠 Conceptual

expert

Understanding side effects in user-defined functions

Which statement about user-defined functions (UDFs) in SQL Server is TRUE?

A UDFs can modify database state by performing INSERT, UPDATE, or DELETE operations.

B UDFs always execute faster than equivalent inline SQL queries.

C Scalar UDFs can call stored procedures that modify data.

D Table-valued UDFs can be used in JOINs like regular tables or views.

💡 Hint

Think about what UDFs are allowed to do and how they behave in queries.

Table-valued functions return tables and can be joined like tables or views. UDFs cannot modify data or call stored procedures that do.

Practice

1. What is the main purpose of a user-defined function in SQL?

easy

A. To create reusable code that returns a single value

B. To store large amounts of data

C. To create tables automatically

D. To delete records from a table

Solution

1. Step 1: Understand what user-defined functions do

   User-defined functions are designed to perform specific tasks and return a single value.

2. Step 2: Compare options with the function's purpose

   Only To create reusable code that returns a single value describes creating reusable code that returns a single value, which matches the function's role.

3. Final Answer:

   To create reusable code that returns a single value -> Option A

4. Quick Check:

   User-defined function = reusable single-value code [OK]

Hint: Functions return one value and reuse code easily [OK]

Common Mistakes:

• Confusing functions with tables or data storage
• Thinking functions delete or modify data directly
• Assuming functions return multiple rows or tables

2. Which of the following is the correct way to start creating a scalar user-defined function in SQL?

easy

A. DROP FUNCTION function_name();

B. CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END;

C. SELECT FUNCTION function_name();

D. CREATE TABLE function_name();

Solution

1. Step 1: Identify the correct syntax for creating a function

   The correct syntax starts with CREATE FUNCTION, specifies the return type, and includes a BEGIN...END block.

2. Step 2: Check each option's validity

   CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END; matches the correct syntax. Options A, B, and C are for other SQL commands (dropping, table creation, and selection).

3. Final Answer:

   CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END; -> Option B

4. Quick Check:

   CREATE FUNCTION + RETURNS + BEGIN...END = correct syntax [OK]

Hint: Functions start with CREATE FUNCTION and specify RETURNS [OK]

Common Mistakes:

• Using CREATE TABLE instead of CREATE FUNCTION
• Trying to SELECT a function directly without calling it
• Confusing DROP FUNCTION with creation syntax

3. Given the function below, what will be the output of SELECT dbo.AddFive(10);?

CREATE FUNCTION dbo.AddFive(@num INT) RETURNS INT AS BEGIN RETURN @num + 5; END;

medium

A. 5

B. 10

C. 15

D. Syntax Error

Solution

1. Step 1: Understand the function logic

   The function takes an integer input and returns the input plus 5.

2. Step 2: Calculate the output for input 10

   10 + 5 = 15, so the function returns 15.

3. Final Answer:

   15 -> Option C

4. Quick Check:

   Input 10 + 5 = 15 [OK]

Hint: Add input to 5 as function returns input + 5 [OK]

Common Mistakes:

• Confusing input and output values
• Assuming function returns input unchanged
• Expecting syntax error due to missing parameters

4. Identify the error in the following user-defined function:

CREATE FUNCTION dbo.MultiplyByTwo(@val INT)
RETURNS INT
BEGIN
  SET @val = @val * 2;
  RETURN @val;
END;

medium

A. Missing AS keyword before BEGIN

B. Functions cannot modify input parameters

C. RETURN statement is not allowed in functions

D. Function must return VARCHAR, not INT

Solution

1. Step 1: Review function syntax rules

   In SQL, the AS keyword is required before the BEGIN block in function definitions.

2. Step 2: Check the given function

   The function is missing AS before BEGIN, causing a syntax error.

3. Final Answer:

   Missing AS keyword before BEGIN -> Option A

4. Quick Check:

   CREATE FUNCTION ... RETURNS ... AS BEGIN ... [OK]

Hint: Always include AS before BEGIN in function syntax [OK]

Common Mistakes:

• Trying to modify input parameters inside functions
• Omitting AS keyword before BEGIN
• Confusing function return types

5. You want to create a user-defined function that returns the length of a string only if the string is not empty; otherwise, it returns 0. Which of the following function definitions correctly implements this logic?

hard

A. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN NULL; END;

B. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN IF @input = '' RETURN 1; ELSE RETURN LEN(@input); END;

C. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN CASE WHEN @input = '' THEN 1 ELSE LEN(@input) END; END;

D. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END;

Solution

1. Step 1: Understand the requirement

   The function must return 0 if the string is empty, otherwise return its length.

2. Step 2: Analyze each option

   CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END; correctly returns LEN(@input), and since LEN('')=0 it satisfies the logic. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN IF @input = '' RETURN 1; ELSE RETURN LEN(@input); END; returns 1 for empty strings, incorrect. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN CASE WHEN @input = '' THEN 1 ELSE LEN(@input) END; END; returns 1 for empty strings using CASE, incorrect. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN NULL; END; always returns NULL, which is incorrect.

3. Final Answer:

   CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END; -> Option D

4. Quick Check:

   LEN('') = 0 satisfies condition [OK]

Hint: LEN('') returns 0 for empty strings [OK]

Common Mistakes:

• Returning incorrect value (like 1) for empty strings
• Using overly complex conditional logic unnecessarily
• Returning NULL instead of 0 for empty strings