SQL Server – Stored Procedures

What is a Stored Procedure?
Ans: A Stored Procedure is a collection or a group of T-SQL statements. Stored Procedures are a precompiled set of one or more statements that are stored together in the database, which can be stored and used repeatedly.
Stored procedures can be called and executed as and when required. They reduce the network load because of the precompilation.
Stored procedures are stored as 'Named object' in the SQL server database.
The result set of the stored procedure depends on the values of the parameters.

What is a Recursive Stored Procedure?
Ans: SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.
There are two forms of recursive stored procedures – mutual recursion and chain recursion.

CREATE PROCEDURE [dbo].[Fact]
(@Number Integer,@RetVal Integer OUTPUT)
AS
  DECLARE @In Integer
  DECLARE @Out Integer
  IF @Number != 1
    BEGIN
      SELECT @In = @Number -1
      EXEC Fact @In, @Out OUTPUT -- Same stored procedure has been called again(Recursively)
      SELECT @RetVal = @Number * @Out
    END
ELSE
  BEGIN
    SELECT @RetVal = 1
  END
RETURN
GO

What are the advantages of using stored procedures in an SQL server?
Ans: Few Advantages are: -
1. Stored procedures provide faster performance since they are precompiled and stored in executable form.
2. A stored procedure is nothing but a piece of code that can be used many times, increasing productivity.
3. It can be stored in a database server instead of on a client machine. As a result, it increases the speed of query execution.
4. The most important use of a Stored Procedure is for security purposes. They can restrict SQL Injection. We can avoid SQL injection by use of a Stored Procedure.

How can you improve the performance of stored procedures in the SQL server?
A. Use SET NOCOUNT ON: This statement is used to stop the message, which shows the number of rows affected by SQL statement like INSERT, UPDATE and DELETE. It will reduce network traffic and increase operational performance.
B. Use schema name before objects: It helps SQL Server to find the object. Example: SELECT StudentId, StudentName FROM dbo.Student
C. Use EXISTS () instead of COUNT (). If you want to check if a record exists, use EXISTS () instead of COUNT (). While COUNT () scans the entire table. EXISTS will give you better performance.

IF(NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'student'))  
BEGIN  
-- your logic
END  

D. Don’t use functions in the WHERE clause: Using functions in where condition will reduce the performance of your query. Try to avoid the function in where clause
E. Use NO LOCK: Using NOLOCK will improve the performance of the select query
SELECT StudentId,StudentName FROM dbo.Student WITH(NOLOCK)  WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)
F. Specify column names instead of using * in SELECT statement

-Try to avoid *
SELECT * FROM dbo.Student WITH(NOLOCK)  WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)
--Best practice is to specify the column name.
SELECT StudentId,StudentName FROM dbo.Student WITH(NOLOCK)  WHERE Convert(varchar(12),ip.CreatedDate,101)=Convert(varchar(12),GETDATE(),101)

G. Avoid temp temporary table: Temporary tables usually increase a query’s complexity. It’s suggested to avoid the temporary tables.
H. Create Proper Index: Proper indexing will improve the speed of the operations in the database.
I. Use Join query instead of sub-query and co-related subquery: Using JOIN is better for the performance than using subqueries or nested queries
J. When fully qualified procedure name, the SQL server finds the compiled plan quickly, which in turn increases the performance
K. Specifying stored procedures as sp_procedurename must be avoided because the SQL server will search the master database first if it finds ‘sp’ in the procedure name. It would decrease the performance and yield error results at times.
L. Transactions need to be shorter so that deadlocks and blocking can be prevented.

What are Differences between Stored Procedure and Function in SQL Server?
1. The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.
2. Functions can have only input parameters for it whereas Procedures can have input or output parameters.
3. Functions can be called from Procedure whereas Procedures cannot be called from a Function.
4. The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
5. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement
6. An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
7. We can use Transactions in Procedure whereas we can't use Transactions in Function.

Is it possible to call a stored procedure within a stored procedure?
Ans: Yes, we can call a stored procedure within a stored procedure. It is called the recursion property of the SQL server and these types of stored procedures are called nested stored procedures.

What is the use of an Extended Stored Procedure?
Ans: Generally, stored procedures are the blocks of codes that can be accessed by simple application calls. At the same time, extended stored procedures help expand the functionality of SQL servers through external functions written in C or C++ languages. As a result, it allows returning result sets and parameters to the server from different data sources

What is the command used to Recompile the stored procedure at run time?
Ans: Stored Procedure can be executed with the help of keyword called RECOMPILE.

2. What is the use of SET NOCOUNT ON/OFF statement?
Ans. By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed.
If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).

What are the differences between Stored Procedure and the dynamic SQL?
Ans: Stored Procedure is a set of statements which is stored in a compiled form.
Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.

What are the Advantages of Stored Procedures over Dynamic SQL?
1. A stored procedure is cached in server memory, so it is faster than dynamic SQL
2. Stored procedures keep ‘business logic’ separate from ‘database logic’. So, if any error occurs in the business logic, you have to change the application codes only. Similarly, if there is an issue with the database logic, you have to change the stored procedures only.
3. The stored procedure creates low network traffic, whereas dynamic SQL creates high network traffic.
4. Stored procedures with static SQL can detect errors before they run, whereas stored procedures with dynamic SQL cannot detect errors before they run.
5. It can be used in many applications after the successful compilation
6. A stored procedure is nothing but a piece of code that can be used many times, increasing productivity.