Select chapter



Basics (2.papers)
Data Types (2.papers)
Table-designing (1.papers)
Queries-Commands (3.papers)
String functions (2.papers)
Subqueries (1.papers)
Joins (1.papers)
Constraints (1.papers)
Triggers (1.papers)
Hide

Sponsored Links


  

sql stored procedures interview questions and answers

1
What is a stored procedure?
  1. A stored procedure is a subroutine which contains set of SQL statements.
  2. A stored procedure is a compiled query(whose execution plan is cached in the SQL Server).
  3. A stored procedure usually reduces the network traffic.
  4. All the options are correct.
Show Answer
Share Facebook gmail Twitter
Show Answer
Share Facebook gmail Twitter
3
Which of the following statements are allowed in stored procedures?
  1. data manipulation language statements ( ex .. Insert , update ,delete queries )
  2. data definition language statements ( create , alter etc…)
  3. data query language statements like select.
  4. All the options are correct.
Show Answer
Share Facebook gmail Twitter
4
Identify the correct syntax for declaring stored procedure?
  1. create proc <procedure_name>
    (
      @variable_name1 data_type [in/output],
    ………….
    )
    as
    begin
      --set of sql statements 
    end
  2. create procedure <procedure_name>
    (
      @variable_name1 data_type [in/output],
    ………….
    )
    as
    begin
      --set of sql statements 
    end
  3. First and second options are correct.
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
5
Which of the following statements is correct about stored procedures?
  1. Input and output parameters are allowed in stored procedures.
  2. Stored procedures can contain dml, ddl and dql or any other T-SQL statements.
  3. a stored procedure can call another stored procedure or user defined function.
  4. All the options are correct.
Show Answer
Share Facebook gmail Twitter
Please read the below given table for solving questions from 6 to 15
eidfnamelnameagesalarydeptdoj
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM
2sowmyakumari2319000db13-Nov-10 12:00:00 AM
3kishorekumar2736000android16-Oct-11 12:00:00 AM
4abimanyubiswal22nullandroid25-May-10 12:00:00 AM
5sonikumar2421800.net21-Jun-09 12:00:00 AM
6anu_singh2212000db23-Oct-10 12:00:00 AM
7_dineshmoh%anty2315000.net26-Aug-09 12:00:00 AM
8nischala_kumari2218000db19-Jul-08 12:00:00 AM
9gouravchourasia2420000android06-Oct-13 12:00:00 AM
Note: Assume table is present in a database named skillgun , the Schema name is dbo and the table name as Employees ;
6
Which of the following stored procedures return employees full name for the given employee eid?
  1. create proc GetEmployeeFullname
    (
       @eid int
    )
    as
    begin
    select fname+lname as 'fullname' from employees where eid=@eid ;
    end
  2. create proc GetEmployeeFullname
    (
       @eid int
    )
    as
    begin
    declare @fullname varchar(40) ;
    select @fullname= fname+lname from employees where eid=@eid ;
    return @fullname
    end
  3. create proc GetEmployeeFullname
    as
    begin
    declare @eid int ;
    set @eid=10 ;
    select fname+lname as 'fullname' from employees where eid=@eid ;
    end
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
7
Which of the following stored procedures return all employee details for the given employee eid?
  1. create proc GetEmployeeFullname
    (
       @eid int
    )
    as
    begin
    select fname+lname as 'fullname' from employees where eid=@eid ;
    end
  2. create proc GetEmployeeFullname
    (
       @eid int
    )
    as
    begin
    select * from employees where eid=@eid ;
    end
  3. create proc GetEmployeeFullname
    as
    begin
    select * from employees;
    end
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
8
In how many ways, data can be returned from stored procedures?
  1. 1 way (that is by using select statement )
  2. 2 ways ( one method is by using select statements and second way is by using return statement )
  3. 3 ways ( using select statement , return statement and by using output parameters )
  4. Stored procedures can never return data .
Show Answer
Share Facebook gmail Twitter
9
Which of the following stored procedure is suitable for inserting new employee details into employees table ?
  1. create proc AddEmployee
    (
       @eid int , @fn varchar(40) ,@ln varchar(40) , @age int ,@sal int ,
       @dept varchar(40),@doj date 
    )
    as
    begin
      insert into employees values (@eid,@fn,@ln,@age,@sal,@dept,@doj) ;
    end
  2. create proc AddEmployee
    as
    begin
       declare @eid int ; declare @fn varchar(40) ;declare @ln varchar(40) ; 
       declare @age int ;
       declare @sal int ;
       declare @dept varchar(40); declare @doj date ;
      insert into employees values (@eid,@fn,@ln,@age,@sal,@dept,@doj) ;
    end
Show Answer
Share Facebook gmail Twitter
Show Answer
Share Facebook gmail Twitter
Show Answer
Share Facebook gmail Twitter
12
How to call a stored procedure with output parameters (identify the correct syntax for calling the below given stored procedure)?
Stored proc:
create proc GetEmployeeDetails
( 
  @eid int , 
  @fullname varchar(40)output,
  @age int output, 
  @sal int output 
)
as
begin
  select @fullname=fname+lname , @age=age, @sal=salary from employees 
  where eid=@eid;
end
  1. exec GetEmployeeDetails 1
  2. declare @fn varchar(40) ; declare @age int ; declare @sal int ;
    exec GetEmployeeDetails 1,@fn =@fullname output,@age=@age output ,@sal=@sal output
    select @fn,@age,@sal
  3. declare @fn varchar(40) ; declare @age int ; declare @sal int ;
    exec GetEmployeeDetails 1,@fullname=@fn output,@age=@age output ,@sal=@sal output
    select @fn,@age,@sal
  4. declare @fn varchar(40) ; declare @age int ; declare @sal int ;
    exec GetEmployeeDetails 1,@fullname=@fn ,@age=@age ,@sal=@sal 
    select @fn,@age,@sal
Show Answer
Share Facebook gmail Twitter
13
How to check existence of a stored procedure?
  1. if(object_id('procedure_name','p') is not null)
    begin
      --do something
    end
  2. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'procedure_name')
    begin
    --do something
    end
  3. Both first and second options are correct.
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
14
How to call a stored procedure from another stored procedure?
  1. create proc proc1
    as
    begin
      exec proc2
    end
  2. It is not possible to call a stored procedure from another stored procedure.
Show Answer
Share Facebook gmail Twitter
15
Which of the following stored procedure returns data returned by the below given stored proc?
Stored proc :
create proc P1
as
begin
 select fname+lname as 'fullname',age,salary from employees
 end
  1. create proc p2
    as
    begin
      return exec p1 ;
    end
  2. create proc p2
    as
    begin
      select * from p1 ;
    end
    
  3. create proc p3
    as
    begin
      decalre @v table (fn varchar(40),age int , salary int);
    insert into @v exec p1;
    select * from @v ;
    end
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
16
How to display all database names present in SQL Server?
  1. use stored procedure sp_getalldatabases ;
  2. use sp_databases stored procedure ;
  3. use sp_who stored procedure ;
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
17
How to display all columns present in a table?
  1. EXEC sp_columns @table_name = 'tablename'
  2. EXEC sp_columns 'tablename'
  3. EXEC sp_columns ('tablename')
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
18
How to display all the stored procedures present in the current data base?
  1. call sp_storedprocs stored procedure.
  2. call sp_stored_procedures stored procedure.
  3. call sp_all stored procedures.
  4. call sp_procs stored procedure.
Show Answer
Share Facebook gmail Twitter
Read more papers :