This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Stored Procedure – 1”.
1. How many ways of returning data is present in SQL Server 2000?
a) 3
b) 4
c) 5
d) 6
View Answer
Explanation: Microsoft® SQL Server™ 2000 stored procedures return data in four ways.
2. Point out the correct statement.
a) Stored procedures assist in achieving consistent implementation of logic across applications
b) A stored procedure is a group of Transact-SQL statements compiled into a single execution plan
c) Stored procedures can also improve performance
d) All of the mentioned
View Answer
Explanation: The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure.
3. Data return using output parameter is ____________
a) Return codes, which are always an integer value
b) A global cursor that can be referenced outside the stored procedure
c) A single cursor that can be referenced inside the stored procedure
d) None of the mentioned
View Answer
Explanation: Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
4. Which of the following is a system procedure?
a) sp_changeowner
b) sp_owner
c) sp_change
d) all of the mentioned
View Answer
Explanation: sp_changeowner is used to change the owner of an database object.
5. Point out the wrong statement.
a) sp_depends provide the details of all database objects that depend on the specific database object
b) sp_helpdb provides the text of a stored procedure reside in Sql Server
c) Extended procedures provide an interface to external programs for various maintenance activities
d) All of the mentioned
View Answer
Explanation: sp_helptext provides the text of a stored procedure reside in SQL Server.
6. __________ provides details on any database object.
a) sp_changeowner
b) sp_owner
c) sp_change
d) none of the mentioned
View Answer
Explanation: sp_help provides details on any database object such as tables,views and so on.
7. Which of the following is an extended procedure?
a) xp_change
b) sp_owner
c) sp_change
d) xp_logininfo
View Answer
Explanation: xp_logininfo procedure will display details about the BUILTIN\Administrators Windows group.
8. _____________ procedure cannot be created in Resource database.
a) System
b) User defined
c) Extended
d) All of the mentioned
View Answer
Explanation: User defined procedure can be created in all system databases except the Resource database or in a user-defined database.
9. Nesting level of stored procedure is applicable for ____________
a) Triggers
b) Views
c) Functions
d) All of the mentioned
View Answer
Explanation: We can nest stored procedures and managed code references in Sql Server up to 32 levels only.
10. Nesting level of a stored procedure’s execution is stored in the _________ function.
a) @@NEST
b) @@NESTLEVEL
c) @@LEVEL
d) None of the mentioned
View Answer
Explanation: When @@NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
- Apply for Programming Internship
- Check Information Technology Books
- Practice Programming MCQs
- Check SQL Server Books