- Courses
- Tutorials
- Practice
- Jobs
Stored Procedures Quiz
Question 1
What is the most significant, yet subtle, difference between a user-defined stored procedure and a system stored procedure in SQL Server?
- A
Only system stored procedures can alter system tables.
- B
User-defined procedures are compiled at runtime, while system procedures are pre-compiled.
- C
System stored procedures are automatically created with every new database, while user-defined ones are not.
- D
System stored procedures are prefixed with 'sp_', and user-defined procedures cannot use this prefix.
Question 2
Which of the following is a valid way to execute a stored procedure named 'GetEmployee' with a parameter '@EmpID' set to 101?
- A
EXECUTE GetEmployee 101;
- B
CALL GetEmployee @EmpID = 101;
- C
PERFORM PROCEDURE GetEmployee WITH @EmpID = 101;
- D
RUN GetEmployee(101);
Question 3
If a stored procedure has two parameters, '@Param1' and '@Param2', what is the default parameter type if not specified?
- A
RETURN
- B
OUT
- C
IN
- D
INOUT
Question 4
What is the primary purpose of the 'OUTPUT' keyword when defining a parameter in a stored procedure?
- A
To ensure the parameter's value is not null.
- B
To specify that the parameter is optional.
- C
To display the parameter's value on the screen.
- D
To allow the stored procedure to modify the parameter's value and pass it back to the calling code.
Question 5
Which of the following is a key advantage of using stored procedures?
- A
They increase network traffic by sending more data to the server.
- B
They make the database schema more difficult to change.
- C
They can be used to encapsulate business logic and improve security.
- D
They are always faster than ad-hoc queries.
Question 6
How would you correctly call a procedure 'Update_Employee' and pass a value to its output parameter '@NewID'?
- A
EXEC Update_Employee INTO @NewID;
- B
EXEC Update_Employee @NewID;
- C
DECLARE @NewID INT; EXEC Update_Employee @NewID OUTPUT;
- D
EXEC Update_Employee GET @NewID;
Question 7
What is the result of executing a stored procedure that has an 'INOUT' parameter?
- A
The procedure returns a table of values.
- B
The parameter's value can only be read by the procedure.
- C
The procedure can only write a value to the parameter.
- D
The parameter's value can be modified by the procedure and the new value is available to the caller.
Question 8
If you want to create a stored procedure that accepts an optional parameter '@Country' with a default value of 'USA', how would you define it?
- A
CREATE PROCEDURE GetCustomers @Country VARCHAR(50) = 'USA'
- B
CREATE PROCEDURE GetCustomers @Country VARCHAR(50) OPTIONAL 'USA'
- C
CREATE PROCEDURE GetCustomers @Country VARCHAR(50) IS 'USA'
- D
CREATE PROCEDURE GetCustomers @Country VARCHAR(50) DEFAULT 'USA'
Question 9
How can a stored procedure return a single, integer status code to the calling environment?
- A
Using an OUTPUT parameter.
- B
Using the EXIT statement.
- C
Using the RETURN statement.
- D
Using a SELECT statement.
Question 10
What is the purpose of the 'AS' keyword in a 'CREATE PROCEDURE' statement?
- A
It is optional and can be omitted.
- B
It assigns an alias to the stored procedure.
- C
It separates the procedure's parameter definitions from its executable code.
- D
It specifies the security context in which the procedure will run.
There are 10 questions to complete.