T/SQL - Stored Procedures and Functions - Part Three

In this Article we Learn about store procedures and User Defined Function in SQL Server with Example.In previous article,we discussed about T/SQL and Cursor.

In this article, we will learn about stored procedures and user-defined functions in SQL Server with an example. In the previous article, we discussed T/SQL and Cursor.

If you want to read the previous articles of this series, then please see these Links,

Introduction

Stored Procedure

A stored procedure is a database object which contains Precompiled SQL Queries. Stored Procedure is a block of code designed to perform a task whenever needed.

Whenever we want to execute an SQL Query from an application the query will be first compiled for execution where the process of compiling is time-consuming because compiling occurs each and every time we execute the query.

To overcome this problem, we add SQL stints or queries under Stored Procedure and execute because a stored procedure is a pre-compiled block of code without compiling. The query gets executed whenever the procedure is called which increases the performance of the application.

Once the procedure is created, it is physically saved on the server as a database object which can be called whenever we require.

We can call the procedure from any application developed by using .NET or Java, PHP.

Advantages of Store Procedures

  1. The application performance will be improved because there is no unnecessary compilation of queries.
  2. Code reusability and security.

We can classify the stored procedure in two ways.

  1. Stored Procedures with parameters
  2. Stored Procedures without parameters.

Stored Procedures with parameters

  1. Create Procedure <Proc Name>  
  2. @<Variable/Parameters Name> [Datatype]  
  3. As  
  4. Begin  
  5. <Query/Statement>  
  6. End  

Stored Procedures without parameters 

  1. Create Procedure <Proc Name>  
  2. As  
  3. Begin  
  4. <Query/Statement>  
  5. End  

Syntax to call a stored procedure

Execute/Exec <Proc Name> Parameters.

Example

Create a procedure to print a message.

  1. Create procedure Sp_message  
  2. As  
  3. Begin  
  4. Print 'Welcome to C# Corner'  
  5. End  

Exec Sp_message 

Result

Result
Example

Create a procedure for adding two numbers.
  1. Create Proc Add1  
  2. @a int,@b int  
  3. As  
  4. Begin  
  5. Declare @c int;  
  6. Declare @d int;  
  7. Set @c= @a+@b  
  8. Set @d=@a-@b  
  9. Print 'Add is :'+cast(@c as char)  
  10. End  
Result

Result 

User-Defined Function or Store Function: Function is a block of code similar to procedures which can be used to perform some operations and return a single value to the user.

Types of Functions

  1. Scalar-valued Function
  2. Table-valued Function

Scalar-valued Function

In this case, the function returns a single column value to the user.

Syntax

  1. create function <Function name>(@parameter)[Datatype]  
  2. Returns (Column Datatype)  
  3. As  
  4. Begin  
  5. Return (column Datatype)  
  6. End  

Syntax to call a scalar-value function

Select dbo.function name (value)

Example

Create a svf to return the cube of the given value.

  1. Create function sfunction(@x int)  
  2. Returns int  
  3. As  
  4. Begin  
  5. Return @x *@x*@x  
  6. End  
  7. Select dbo.sfunction (2)  
  8. Result   

Table Value Function

In this case the function will return more than one column from the table.

Syntax

  1. Create function <Fname>(@parameter)[Datatype]  
  2. Returns Table  
  3. As  
  4. Return (<select statement>)  

Syntax to drop a Function

Drop function <Fname>

Difference Between Function and Procedures

A function must return a value whereas procedure never returns a Value.

A procedure can have a parameter of both input and output whereas a function can have input parameters only.

In a procedure, we can perform select, insert, update, and delete operations whereas the function can be used only to perform select.

We can call a procedure using Execute or Exec command whereas the function is called using the Select command.

Summary

In this article, we learned about stored procedure and user-defined functions and the differences between procedures and functions.