MCQs on Stored Procedures and Functions | SQL Server

Stored procedures and functions in SQL Server enhance database functionality by encapsulating logic and operations. These 30 MCQs cover stored procedures, parameters, user-defined functions, and system stored procedures.

---

Stored Procedures and Functions | SQL Server

Creating and Executing Stored Procedures

1. Which command is used to create a stored procedure in SQL Server?
   • a) CREATE FUNCTION
   • b) CREATE PROC
   • c) CREATE PROCEDURE
   • d) CREATE SP
2. What is the correct syntax to execute a stored procedure named GetEmployeeDetails?
   • a) EXEC GetEmployeeDetails
   • b) RUN GetEmployeeDetails
   • c) CALL GetEmployeeDetails
   • d) EXECUTE PROCEDURE GetEmployeeDetails
3. What happens if a CREATE PROCEDURE statement is executed with a name that already exists?
   • a) The existing procedure is overwritten
   • b) SQL Server throws an error
   • c) The new procedure is created with a duplicate name
   • d) The existing procedure is deleted
4. Which keyword is used to end a block of code within a stored procedure?
   • a) END
   • b) RETURN
   • c) FINISH
   • d) COMPLETE
5. What does the WITH RECOMPILE option in a stored procedure do?
   • a) Prevents execution of the procedure
   • b) Forces recompilation of the execution plan on every run
   • c) Deletes the existing procedure and recreates it
   • d) Optimizes the execution plan permanently

Input and Output Parameters

6. What is the purpose of an input parameter in a stored procedure?
   • a) To store the result of a query
   • b) To pass values into the procedure
   • c) To display the result of the procedure
   • d) To modify the procedure logic
7. Which keyword is used to define an output parameter in a stored procedure?
   • a) RETURN
   • b) OUT
   • c) OUTPUT
   • d) EXIT
8. What will happen if a stored procedure with an output parameter is executed without assigning a variable to the output?
   • a) The procedure fails to execute
   • b) The output is returned to the caller
   • c) The output is ignored
   • d) An error is raised
9. How do you call a stored procedure with an input parameter?
   • a) EXEC ProcedureName VALUE
   • b) CALL ProcedureName (VALUE)
   • c) EXEC ProcedureName @ParameterName = VALUE
   • d) RUN ProcedureName @ParameterName = VALUE
10. What happens when the RETURN keyword is used in a stored procedure?
    • a) Returns a value to the caller
    • b) Exits the procedure immediately
    • c) Both a and b
    • d) Ends the SQL connection

User-Defined Functions (UDFs)

11. What is the difference between a scalar UDF and a table-valued UDF?
    • a) Scalar UDFs return single values; table-valued UDFs return tables
    • b) Scalar UDFs require input parameters; table-valued UDFs do not
    • c) Scalar UDFs cannot use SELECT statements; table-valued UDFs can
    • d) Scalar UDFs modify tables; table-valued UDFs do not
12. Which keyword is used to create a scalar UDF?
    • a) CREATE PROCEDURE
    • b) CREATE FUNCTION
    • c) CREATE TABLE FUNCTION
    • d) CREATE UDF
13. What is mandatory when creating a UDF in SQL Server?
    • a) The function must return a value
    • b) The function must include a TRY-CATCH block
    • c) The function must accept parameters
    • d) The function must create a temporary table
14. Which of the following is a characteristic of UDFs?
    • a) UDFs can return multiple result sets
    • b) UDFs cannot modify database objects
    • c) UDFs can have side effects on the database
    • d) UDFs cannot be used in SELECT statements
15. What is the primary use of an inline table-valued function?
    • a) To create temporary tables
    • b) To return table data as a result of a single query
    • c) To optimize JOIN operations
    • d) To perform batch updates

System Stored Procedures

16. Which system stored procedure is used to list all user-defined stored procedures?
    • a) SP_HELPTEXT
    • b) SP_STORED_PROCS
    • c) SP_HELPROCEDURE
    • d) SP_HELP
17. What does the SP_RENAME system procedure do?
    • a) Renames a stored procedure
    • b) Renames any database object, including tables and columns
    • c) Renames only database tables
    • d) Renames only database schemas
18. Which system stored procedure retrieves the definition of a stored procedure?
    • a) SP_HELPTEXT
    • b) SP_PROCEDURE_TEXT
    • c) SP_GETTEXT
    • d) SP_LIST_PROCS
19. What does the system procedure SP_WHO display?
    • a) A list of currently running stored procedures
    • b) Information about current SQL Server users and sessions
    • c) A list of database objects owned by a user
    • d) A summary of database size and usage
20. Which system procedure is used to create a new login in SQL Server?
    • a) SP_CREATELOGIN
    • b) SP_ADDLOGIN
    • c) SP_LOGINCREATE
    • d) SP_ADDUSER
21. What is the purpose of SP_DEPENDS in SQL Server?
    • a) Lists the dependencies of a database object
    • b) Lists all dependent stored procedures
    • c) Shows which users depend on a schema
    • d) Returns the size of dependent tables
22. Which system stored procedure is used to configure SQL Server options?
    • a) SP_CONFIGURE
    • b) SP_OPTIONS
    • c) SP_SETTINGS
    • d) SP_UPDATE
23. What does the system procedure SP_EXECUTESQL do?
    • a) Executes dynamic SQL queries
    • b) Executes stored procedures only
    • c) Executes parameterized queries only
    • d) Executes predefined SQL scripts
24. What does SP_TABLES return when executed?
    • a) A list of all system tables
    • b) A list of user-defined and system tables in a database
    • c) A list of indexed tables
    • d) A summary of table sizes
25. Which system stored procedure lists all the columns of a table?
    • a) SP_COLUMNS
    • b) SP_TABLE_COLUMNS
    • c) SP_DESCRIBE
    • d) SP_DETAILS

---

Answer Key

Qno	Answer (Option with text)
1	c) CREATE PROCEDURE
2	a) EXEC GetEmployeeDetails
3	b) SQL Server throws an error
4	a) END
5	b) Forces recompilation of the execution plan on every run
6	b) To pass values into the procedure
7	c) OUTPUT
8	c) The output is ignored
9	c) EXEC ProcedureName @ParameterName = VALUE
10	c) Both a and b
11	a) Scalar UDFs return single values; table-valued UDFs return tables
12	b) CREATE FUNCTION
13	a) The function must return a value
14	b) UDFs cannot modify database objects
15	b) To return table data as a result of a single query
16	d) SP_HELP
17	b) Renames any database object, including tables and columns
18	a) SP_HELPTEXT
19	b) Information about current SQL Server users and sessions
20	b) SP_ADDLOGIN
21	a) Lists the dependencies of a database object
22	a) SP_CONFIGURE
23	a) Executes dynamic SQL queries
24	b) A list of user-defined and system tables in a database
25	a) SP_COLUMNS