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.


Related Posts