MCQs – Stored Procedure and SQL function

MCQ – Multiple choice questions on stored procedure sql and function with answers and explanation. Objective questions are asked in Software jobs written interviews.

Q) A stored procedure in SQL is a___________

1. Block of functions
2. Group of SQL statements.
3. None

Answer: 2

Q) Advantage of SQL stored procedure

1. Maintainability
2. Re-use of code
3. Security
4. All

Answer: 4

Q) Syntax to create stored procedure is/are

1. CREATE PROCEDURE procedureName AS
    SQL statements
   

2. CREATE PROCEDURE procedureName AS
   BEGIN
   SQL statements
   END
   

3. CREATE PROC procedureName AS
   BEGIN
   SQL statements
   END
   

4. B & C
5. B only

Answer: 4

Q) Which statement(S) is/are incorrect

1. Stored procedure may return a value and function must return a value.
2. Function has only IN parameter.
3. Try and Catch can be used with both stored procedure and function.
4. Stored procedure has IN and OUT parameter.

Answer: 3

Q) Which statement(S) is/are incorrect

1. Stored procedure can be shared by multiple programs
2. Stored procedures are in compiled form.
3. Stored procedure is a group of SQL statements
4. All are correct.

Answer: 4

Q) Which SQL Queries to execute stored procedure given below is/are incorrect

Create Procedure spGetStudentByGenderAndAge
@Gender nvarchar(10), @Age int
As
Begin
Select Name, Gender, Age from tblStudent Where Gender = @ Gender
And Age = @ Age
End 

1. spGetStudentByGenderAndAge 1, ‘male’
2. spGetStudentByGenderAndAge ‘male’ 1
3. spGetStudentByGenderAndAge @ Age =1, @ Gender =’male’
4. spGetStudentByGenderAndAge @ Gender =’male’, @ Age =1

Answer : A.

Error converting nvarchar to int. Rest are fine.