This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Built in Stored Procedures”.
1. Stored procedure is a __________ set of one or more SQL statements.
a) interpreted
b) compiled
c) pre compiled
d) none of the mentioned
View Answer
Explanation: Stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server.
2. Point out the wrong statement.
a) Stored procedure can accepts input and output parameters
b) Stored procedure can returns multiple values using input parameters
c) Using stored procedure, we can Select,Insert,Update,Delete data in database
d) None of the mentioned
View Answer
Explanation: Stored procedure can returns multiple values using output parameters.
3. How many types of stored procedures are present in SQL Server?
a) 3
b) 4
c) 5
d) 8
View Answer
Explanation: SQL Server is packed full of useful system stored procedures there for its own use that you can use as well.
4. Which of the following procedures are created by user for own actions?
a) User Defined Stored Procedure
b) Extended Procedure
c) CLR Stored Procedure
d) All of the mentioned
View Answer
Explanation: User Defined Stored Procedure can be created in all system databases except the Resource database or in a user-defined database.
5. Point out the wrong statement.
a) A stored procedure is nothing more than prepared SQL code
b) We can create a Stored Procedure using the Create proc statement
c) We can create a Stored Procedure using the Create stor_proc statement
d) All of the mentioned
View Answer
Explanation: Benefit of Stored Procedures is that they are executed on the server side and perform a set of actions, before returning the results to the client side.
6. Which of the following stored procedure is already defined in Sql Server?
a) User Defined Stored Procedure
b) Extended Procedure
c) CLR Stored Procedure
d) System defined Procedure
View Answer
Explanation: System defined Procedure procedure starts with the sp_ prefix.
7. System defined Procedure logically appear in ____________
a) sys schema
b) stor schema
c) proc schema
d) all of the mentioned
View Answer
Explanation: System defined Procedure are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database.
8. Extended procedures starts with the __________ prefix.
a) sp_
b) xp_
c) clr_
d) all of the mentioned
View Answer
Explanation: Extended procedures provide an interface to external programs for various maintenance activities.
9. Type of procedure that are based on the CLR (Common Language Runtime) is ____________
a) User Defined Stored Procedure
b) Extended Procedure
c) CLR Stored Procedure
d) System defined Procedure
View Answer
Explanation: CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#.
10. Nesting limit OF stored procedure is up to ________level.
a) 30
b) 32
c) 34
d) 36
View Answer
Explanation: In Sql Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked with in a store.
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]
- Check Information Technology Books
- Apply for Programming Internship
- Practice Programming MCQs
- Check SQL Server Books
