Stored Procedure and Functions

Stored Procedure

A Stored Procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a Stored Procedure and then just call the Stored Procedure to execute the SQL code that you saved as part of the Stored Procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the Stored Procedure, so depending on what the need is, the Stored Procedure can act accordingly based on the parameter values that were passed.

Stored Procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determine which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a Stored Procedure, they become part of a single execution plan on the server. The results do not need to be returned to the client to have the conditional logic applied; all of the work is done on the server.

Benefits of Stored Procedures 

• Precompiled execution
  
  SQL Server compiles each Stored Procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.
  
  
• Reduced client/server traffic
  
  If network bandwidth is a concern in your environment then you'll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.
  
  
• Efficient reuse of code and programming abstraction
  
  Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you'll find the development cycle requires less time.
  
  
• Enhanced security controls
  
  You can grant users permission to execute a Stored Procedure independently of underlying table permissions.

User Defined Functions

Like functions in programming languages, SQL Server User Defined Functions are routines that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

Functions in programming languages are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

SQL Server supports two types of functions 

• Built-in functions
  
  Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference.
  
  
• User Defined Functions
  
  Allow you to define your own Transact-SQL functions using the CREATE FUNCTION statement. User Defined Functions use zero or more input parameters, and return a single value. Some User Defined Functions return a single, scalar data value, such as an int, char, or decimal value.

Benefits of User Defined Functions

• They allow modular programming
  
  You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.
  
  
• They allow faster execution
  
  Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
  
  
• They can reduce network traffic
  
  An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.

Differences between Stored Procedure and User Defined Function in SQL Server

User Defined Function	Stored Procedure
Function must return a value.	Stored Procedure may or not return values.
Will allow only Select statements, it will not allow us to use DML statements.	Can have select statements as well as DML statements such as insert, update, delete and so on
It will allow only input parameters, doesn't support output parameters.	It can have both input and output parameters.
It will not allow us to use try-catch blocks.	For exception handling we can use try catch blocks.
Transactions are not allowed within functions.	Can use transactions within Stored Procedures.
We can use only table variables, it will not allow using temporary tables.	Can use both table variables as well as temporary table in it.
Stored Procedures can't be called from a function.	Stored Procedures can call functions.
Functions can be called from a select statement.	Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
A UDF can be used in join clause as a result set.	Procedures can't be used in Join clause

CTE, Temp Table and table variable

CTE - Common Table Expressions CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

When to use CTE? This is used to store result of a complex sub query for further use.
This is also used to create a recursive query.
Temporary Tables In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-
Local Temp Table Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from #LocalTemp

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
Global Temp Table Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from ##GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
Table Variable This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct

 --Next batch
 GO
 Select * from @TProduct --gives error in next batch

Note Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

Where and Having Clause in SQL

WHERE and HAVING clause are mainly used in the statement of SQL queries, these allow us to restrict the combination in the result relation through using a specific predicate. The major difference between WHERE and HAVING is that WHERE clause specifies the conditions for selecting the tuples (rows) from the relations, including join conditions if needed. On the other hand, HAVING clause specifies a condition on the groups being selected rather than on individual tuples.

SQL stands for Structured Query Language; it is comprehensive or declarative database language used for accessing the data from the databases.

Comparison Chart

Basis for comparison	WHERE	HAVING
Basic	Implemented in row operations.	Implemented in column operations.
Applied to	Single row	Summarized row or groups.
Data fetching	It only fetches the particular data from particular rows according to the condition.	At first, complete data is fetched then separated according to the condition.
Aggregate Functions	Cannot appear in WHERE clause.	Can appear in HAVING clause.
Used with	SELECT and other statements such as UPDATE, DELETE or either one of them.	Can't be used without a SELECT statement.
Act as	Pre-filter	Post-filter
GROUP BY	Comes after WHERE.	Comes before HAVING.

Definition of Where Clause

The SQL WHERE clause is used to describe a condition at the time of retrieving the data from a single table or by joining with multiple tables.  It only returns the particular value from the table if the given condition is fulfilled. WHERE clause is used to permeate the records and retrieve only the necessary records.

SQL also implements the logical connectives and, or and not in the WHERE clause which are also known as the boolean condition; the condition must be true to retrieve the tuples. The operands of the logical connectives expressions involve the comparison operators such as <, <=, >, >=, =, and <>. These comparison operators compare strings and arithmetic expressions. It can be used in the SELECT statement, as well as in UPDATE, DELETE statements.

Let’s take an example. The table shown below named as ‘Sales‘ table consists of ‘Product‘ and ‘Sales_amount‘ attributes.

The following query is to be written to calculate the Total_sales of phone and speakers.

1. SELECT Product, sum(Sales_amount) AS Total_sales
2. FROM Sales
3. WHERE Product in ( 'Phone', 'Speakers')
4. GROUP BY Product;

Following output is the resulting output where the rows are filtered first, phone and speaker rows are retrieved then the aggregate function is performed.

Definition of Having Clause

SQL provides HAVING clause which can be used in conjunction along with GROUP BY clause. This HAVING clause helps in retrieving the values for the groups that fulfils certain conditions. WHERE clause can also use in conjunction with HAVING clause during selection, WHERE clause filters the individual row. The rows are then grouped, and aggregate calculations are performed, at last HAVING clause filter the groups.

It behaves in the same as WHERE when GROUP BY keyword is not used. The Group functions such as min, max, avg, sum, and count can appear in only two clauses: SELECT and HAVING clause. It provides a condition on the tuples corresponding to each value in the group of attributes. The only set of records that satisfies the condition will be shown as the result.

Here also we are taking the same example as WHERE clause and considering the same ‘Sales‘ table. When we want to calculate the Total_sales of phone and speakers using HAVING clause, we will write the following query.

1. SELECT Product, sum(Sales_amount) AS Total_sales
2. FROM Sales
3. GROUP BY Product
4. HAVING Product in ('phone', 'Speakers');

The query produces the following output where the products are retrieved first, then aggregate function (sum) is performed and at last groups are filtered unlike WHERE clause.

When we want to find only those products where the Total_sales is greater than 1000. The query can be written as:

1. SELECT Product, sum(Sales_amount) AS Total_sales
2. FROM Sales
3. GROUP BY Product
4. HAVING sum(Sales_amount)>1000;

The produced output is:

This can not be performed using WHERE clause in spite of HAVING and it generates an error message because WHERE clause cannot be used with aggregate functions.

Key Differences Between Where and Having Clause

1. WHERE clause is employed in row operations and applied on a single row whereas HAVING clause is used in column operations and can be applied to summarized rows or groups.
2. In WHERE clause the desired data is fetched according to the applied condition. In contrast, HAVING clause fetch whole data then separation is done according to the condition.
3. Aggregate functions like min, sum, max, avg can never appear along with WHERE clause. As against, these functions can appear in HAVING clause.
4. HAVING clause can not use without a SELECT statement. Conversely, WHERE can be used with SELECT, UPDATE, DELETE, etc.
5. WHERE clause behaves as a pre-filter while HAVING clause acts as a post-filter.
6. WHERE clause when used with GROUP BY, comes before GROUP BY. This signifies that WHERE filter rows before aggregate calculations are performed. On the other hand, HAVING comes after GROUP BY, which means it filters after aggregate calculations are performed.

Conclusion

WHERE and HAVING clause works in the same way except the additional feature that HAVING clause is popular for. HAVING clause can efficiently work with aggregate functions while WHERE cannot be operated with aggregate functions.