SQL Server Interview Day - 4

October 26, 2017 0 Comments A+ a-

1) What is a stored procedure?

A Stored Procedure is a collection or a group of precompiled T-SQL statements are stored together in the database.
It will reduce the network trafic because of the precompilation.

2) Why you use Stored Procedure ?

  • If we use the stored procedure then we will get below advantage.
  • It will reduce the network trafic.
  • It will also reduce the execution time due to precompiled.
  • If will not allow for SQL Injection.
  • Code will stored in server only so indectly We will achieve the security.

3) How can I write a new stored procedure with appropriate example?

Please follow the below example.
CREATE PROCEDURE SP_EmpDetails
AS
BEGIN
SELECT * from tbl_Emp
END

4) What is advantages of using stored procedure in sql server ?

or

What is advantage of function over stored procedure in sql server ?


  • Reuseble the code due to stored in database server.
  • It is very easy to maintain a stored procedure
  • It is preventing the SQL Injection Attacks becasue encrypted code.
  • It is also reduce the execution time and network trafic due to precompiled.

5) How can I pass a parameter to stored procedure in SQL Server ?

Please follow the below example.
CREATE PROCEDURE SP_EmpDetails
@EName VARCHAR(50)
AS
BEGIN
SELECT * from tbl_Emp
WHERE EName = @EName
END

6) How can I excute a stored procedure in SQL Server ?

Please follow the below example.
EXEC SP_EmpDetails 'Tutorials Life'
OR
EXECUTE SP_EmpDetails 'Tutorials Life'

7) How can I pass a output parameter to stored procedure in SQL Server ?

Please follow the below example.
CREATE PROCEDURE SP_EmpDetails
@EName VARCHAR(50),
@EmpCount int Output
AS
BEGIN
SELECT @EmpCount=count(*) from tbl_Emp
WHERE EName = @EName
END

8) How can I excute a stored procedure with output parameter in SQL Server ?

Please follow the below example.
DECLARE @EMPCOUNT int
EXECUTE SP_EmpDetails 'Tutorials Life', @EMPCOUNT
IF(@EMPCOUNT is null)
PRINT '@@EMPCOUNT is null'
ELSE
PRINT @EMPCOUNT

9) What is a sp_depends ?

or

How can I able view the dependencies of the stored procedure ?

Syntax:-
sp_depends SP_Name

  • The above syntax is used to view the dependencies of the stored procedure.
  • It is a system stored procedure.
  • It can able to view the dependencies of other objects like Tables, Views, etc.

10) What is disadvantages/Drawbacks of using stored procedure in sql server ?


  • Limited Coding Functionality such as looping
  • Errors in handling Stored Procedures is poor.
  • maintenance cost is more due to business rules are spread throughout different Stored Procedures.