MS SQL Server - 35 - Stored Procedures MCQ

35 – Stored Procedures MCQ

Best of luck!

1 / 25

1. What is a stored procedure in SQL Server?

A temporary table used for data storage

A function that returns a single value

A precompiled set of SQL statements that can be executed

A trigger that fires automatically on data changes

2 / 25

2. Which SQL statement is used to create a stored procedure in SQL Server?

CREATE PROCEDURE

CREATE TRIGGER

CREATE TABLE

CREATE FUNCTION

3 / 25

3. In SQL Server, what is the primary advantage of using stored procedures?

They can replace the need for tables.

They are automatically executed when data changes.

They can be called from any programming language.

They can return multiple result sets.

4 / 25

4. What is the purpose of the EXECUTE statement in SQL Server?

To retrieve data from tables

To create a new stored procedure

To execute a stored procedure

To declare local variables

5 / 25

5. In SQL Server, which of the following is true about the parameters of a stored procedure?

Parameters are optional and not required.

Parameters are placeholders for values passed into the procedure.

Parameters can only be of the INT data type.

Parameters are used for internal calculations only.

6 / 25

6. What is the primary use of OUTPUT parameters in SQL Server stored procedures?

To display the procedure's execution plan

To specify the return value of the procedure

To pass data from the procedure back to the calling code

To retrieve data from tables

7 / 25

7. In SQL Server, how can you return a result set from a stored procedure?

By using PRINT statements

By using SELECT statements

By using OUTPUT parameters

By using the RETURN statement

8 / 25

8. What is the purpose of the RETURN statement in SQL Server stored procedures?

To terminate the procedure and return control to the caller

To declare local variables

To display a message to the user

To execute a SELECT statement

9 / 25

9. In SQL Server, what happens if a stored procedure does not have a RETURN statement?

The procedure returns an error.

The procedure cannot be executed.

The procedure automatically returns 0.

The procedure returns a NULL value.

10 / 25

10. What is the primary purpose of the AS keyword in a CREATE PROCEDURE statement in SQL Server?

To specify the name of the procedure

To separate the procedure's name and body

To declare local variables

To define the procedure's parameters

11 / 25

11. In SQL Server, what is a parameterized stored procedure?

A procedure that has parameters specified in the CREATE PROCEDURE statement

A procedure that is automatically executed

A procedure that does not require any parameters

A procedure that returns multiple result sets

12 / 25

12. Which of the following is true about input parameters in SQL Server stored procedures?

They are automatically initialized to NULL.

They are used for internal calculations only.

They are optional and not required.

They allow values to be passed into the procedure.

13 / 25

13. In SQL Server, how can you execute a stored procedure that returns a result set?

By using the PRINT statement

By using the EXECUTE statement with the SELECT keyword

By using the EXECUTE statement with the OUTPUT keyword

By using the EXECUTE statement with the INTO keyword

14 / 25

14. What is the purpose of the DROP PROCEDURE statement in SQL Server?

To execute a stored procedure

To create a new stored procedure

To declare local variables

To remove a stored procedure from the database

15 / 25

15. In SQL Server, what is the primary use of the TRY…CATCH block when calling a stored procedure?

To create custom error messages

To handle exceptions and errors in a structured manner

To terminate the procedure

To retrieve data from tables

16 / 25

16. How can you pass values to input parameters when executing a stored procedure in SQL Server?

By specifying the parameter names in the EXECUTE statement

By using the SET statement before executing the procedure

Input parameters cannot receive values when executing a procedure.

By enclosing the values in square brackets

17 / 25

17. What is the purpose of the PRINT statement in SQL Server stored procedures?

To return a result set

To execute a stored procedure

To declare local variables

To display messages in the console

18 / 25

18. In SQL Server, how can you pass a NULL value to an input parameter when executing a stored procedure?

By specifying the parameter name without a value

Input parameters cannot receive NULL values.

By using the SET statement with a value of NULL

By enclosing the value in double quotes

19 / 25

19. In SQL Server, what is the primary purpose of the PRINT statement in a stored procedure?

To retrieve data from tables

To display messages for debugging and informational purposes

To create custom error messages

To terminate the procedure

20 / 25

20. What is the primary advantage of using stored procedures for security in SQL Server?

Stored procedures automatically encrypt data.

Stored procedures can be executed by anyone without authentication.

Stored procedures can restrict access to database objects.

Stored procedures allow for the creation of user-defined functions.

21 / 25

21. In SQL Server, what is the primary purpose of the OUTPUT clause in a stored procedure?

To specify the return value of the procedure

To retrieve data from tables

To terminate the procedure

To display messages to the user

22 / 25

22. What is the primary use of the EXECUTE AS clause in a stored procedure in SQL Server?

To specify the return value of the procedure

To retrieve data from tables

To execute the procedure as a different user or context

To display messages to the user

23 / 25

23. In SQL Server, what is the purpose of the RECOMPILE option for stored procedures?

To specify the return value of the procedure

To automatically execute the procedure when data changes

To create custom error messages

To compile the procedure each time it is executed

24 / 25

24. How can you view the code of an existing stored procedure in SQL Server Management Studio?

Use the SELECT statement

Use the sp_helptext system procedure

Use the PRINT statement

Use the EXECUTE statement

25 / 25

25. What is the primary advantage of using stored procedures for code reusability in SQL Server?

Stored procedures are automatically optimized for performance.

Stored procedures can be reused in different parts of the application.

Stored procedures replace the need for tables.

Stored procedures are automatically executed when data changes.

Your score is

MS SQL Server – 35 – Stored Procedures MCQ