SQL Server Questions and Answers – Stored Procedure – 1

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

Answer: b
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

Answer: d
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

Answer: d
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).
advertisement
advertisement

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

Answer: a
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

Answer: b
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

Answer: d
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

Answer: d
Explanation: xp_logininfo procedure will display details about the BUILTIN\Administrators Windows group.
advertisement

8. _____________ procedure cannot be created in Resource database.
a) System
b) User defined
c) Extended
d) All of the mentioned
View Answer

Answer: b
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

Answer: d
Explanation: We can nest stored procedures and managed code references in Sql Server up to 32 levels only.
advertisement

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

Answer: b
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]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.