Interview Questions

1. Stored procedure can be called from SELECT statement.

Options:

a. TRUE

b. FALSE

Reveal Answer

2. Which statement is true for the following example createproceduresp_emp_sal(@empnoint,@salintoutput) as select@sal=salaryfromemployeewherepkempno=@empno return go -- to execute the procedure declare@empnoint=2 declare@salint execsp_emp_sal@empno,@sal print@sal

Options:

a. The salary of the employee is displayed

b. The OUTPUT keyword doesnot exist in procedures

c. RETURN keyword can not be used in procedures

d. No out put will be got, as @sal doesnot hold any value

Reveal Answer

3. Which statements are true for the following examples alterproceduresp_emp_sal(@empnoint) as declare@salint select@sal=salaryfromemployeewherepkempno=@empno return@sal go declare@empnoint=2 declare@salint exec@sal=sp_emp_sal@empno print@sal

Options:

a. Procedure returns error as RETURN statement cannot be used used in stored procedures

b. exec@sal=sp_emp_sal@empno statement is syntactically wrong so values returned does not come into @sal variable in the calling procedures

c. Successfully executes and displays the salary for a given employee

d. Invalid PRINT statement as the @sal has to be converted into string.

Reveal Answer

4. DML statements are allowed in Stored procedures

Options:

a. TRUE

b. FALSE

Reveal Answer

5. Which statements are true regarding stored procedures

Options:

a. Cannot be utilized in SELECT statement

b. TRY and CATCH block cannot be used in the procedures

c. Procedures cannot be called from functions

d. We can go for transaction management

Reveal Answer

6. Store procedure can be used by

Options:

a. A single user

b. By a single user in a single application

c. By a single user in multiple applications

d. By multiple users in multiple applications

Reveal Answer

7. what are valid parameters in stored procedures

Options:

a. Input parameters

b. Output parameters

c. Only input parameters

d. Default parameters

Reveal Answer

8. OUTPUT parameters are_____________

Options:

a. Uni-directional

b. Bi-directional

Reveal Answer

9. Which are valid system procedure statement to rename a column

Options:

a. sp_rename'employee.fkdeptno','deptno'

b. sp_ename'fkdeptno','deptno','column'

c. sp_rename'employee.fkdeptno','deptno','column

d. sp_renamecol'employee.fkdeptno','deptno'

Reveal Answer

10. The same procedure sp_rename can be used to rename the column and to rename the table also

Options:

a. TRUE

b. FALSE

Reveal Answer

11. Which statements are true regarding stored procedures

Options:

a. SQL query in the stored procedure is parsed and compiled every time they are run

b. A stored procedure is a set of statements/commands which reside in the database.

c. Stored procedures are also useful when you want to do Batch updates on the db.

d. The network traffic reduces due to procedures than executing an adhoc query

Reveal Answer

12. Maximum number of parameters that can be passed to a procedure are

Options:

a. 2150

b. 2010

c. 3150

d. 2100

Reveal Answer

13. The information about the stored procedure can be seen by using _________ procedures

Options:

a. sp_help

b. sp_helptext

c. sp_helpconstraint

d. NONE

Reveal Answer

14. SELECTNAMEFROMSYS.ALL_OBJECTSWHEREtype='P'. This statement gives us________________.

Options:

a. The list of all parameters

b. The list of all functions

c. List of all stored procedures

d. List of all database objects which start with 'p'

Reveal Answer

15. Which statement gives the definition of the procedure?

Options:

a. EXECsp_helptext'sp_insert_empcopy'

b. sp_helptext'sp_insert_empcopy'

c. sp_help'sp_insert_empcopy'

d. None

Reveal Answer

16. Alterprocedureaddition(@num1int,@num2int=10,@num3int=5) as return@num1+@num2+@num3 Is this a valid return statement for stored procedure?

Options:

a. NO, return statement is not allowed in the procedures

b. Return statement is allowed, but it cannot have mathematical expressions

c. YES, it is a valid statement

d. The sum of three values is calculated and the result is returned to the calling procedures

Reveal Answer

17. Which statements are true regarding procedures

Options:

a. The code of the stored procedure can be changed without affecting application.

b. Stored procedure is cached in SQL Server’s memory.

c. By using stored procedures we can reuse the code.

d. All the above

Reveal Answer

18. What information does the following sql query give? SELECT sys.objects.name,sys.objects.type,sys.objects.type_desc, sys.objects.schema_id,sys.syscomments.text FROMsys.objects INNERJOINsys.syscommentsONsys.objects.object_id=sys.syscomments.id wheresys.syscomments.textlike'%employee%' Andtype='P'

Options:

a. The list of parameters which take the input values from the table 'EMPLOYEE'

b. To get the definition of procedures which have 'EMPLOYEE' table used

c. The list of parameters which give the output values from the table 'EMPLOYEE' to output parameters

d. The list of procedures in which the 'EMPLOYEE' is used.

Reveal Answer

19. Functions can be called from procedures. Is this statement true?

Options:

a. YES

b. NO

Reveal Answer

20. Which statement is true regarding procedures

Options:

a. DML statements which can change the existing database are not allowed

b. Only SELECT statement can be used.

c. Procedures are used to perform DML actions

d. Try and catch block cannot be used in procedures

Reveal Answer

Interview Questions and Answer

1. Stored procedure can be called from SELECT statement.

2. Which statement is true for the following example createproceduresp_emp_sal(@empnoint,@salintoutput) as select@sal=salaryfromemployeewherepkempno=@empno return go -- to execute the procedure declare@empnoint=2 declare@salint execsp_emp_sal@empno,@sal print@sal

3. Which statements are true for the following examples alterproceduresp_emp_sal(@empnoint) as declare@salint select@sal=salaryfromemployeewherepkempno=@empno return@sal go declare@empnoint=2 declare@salint exec@sal=sp_emp_sal@empno print@sal

4. DML statements are allowed in Stored procedures

5. Which statements are true regarding stored procedures

6. Store procedure can be used by

7. what are valid parameters in stored procedures

8. OUTPUT parameters are_____________

9. Which are valid system procedure statement to rename a column

10. The same procedure sp_rename can be used to rename the column and to rename the table also

11. Which statements are true regarding stored procedures

12. Maximum number of parameters that can be passed to a procedure are

13. The information about the stored procedure can be seen by using _________ procedures

14. SELECTNAMEFROMSYS.ALL_OBJECTSWHEREtype='P'. This statement gives us________________.

15. Which statement gives the definition of the procedure?

16. Alterprocedureaddition(@num1int,@num2int=10,@num3int=5) as return@num1+@num2+@num3 Is this a valid return statement for stored procedure?

17. Which statements are true regarding procedures

18. What information does the following sql query give? SELECT sys.objects.name,sys.objects.type,sys.objects.type_desc, sys.objects.schema_id,sys.syscomments.text FROMsys.objects INNERJOINsys.syscommentsONsys.objects.object_id=sys.syscomments.id wheresys.syscomments.textlike'%employee%' Andtype='P'

19. Functions can be called from procedures. Is this statement true?

20. Which statement is true regarding procedures

Packages

Resources

About

Other links