T/SQL - Stored Procedures and Functions - Part Three

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,

1. T/SQL-PART ONE
2. T/SQL-PART TWO

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

Create Procedure <Proc Name>
@<Variable/Parameters Name> [Datatype]
As
Begin
<Query/Statement>
End

Stored Procedures without parameters 

Create Procedure <Proc Name>
As
Begin
<Query/Statement>
End

Syntax to call a stored procedure

Execute/Exec <Proc Name> Parameters.

Example

Create a procedure to print a message.

Create procedure Sp_message
As
Begin
Print 'Welcome to C# Corner'
End

Exec Sp_message 

Result

Example

Create a procedure for adding two numbers.

Create Proc Add1
@a int,@b int
As
Begin
Declare @c int;
Declare @d int;
Set @c= @a+@b
Set @d=@a-@b
Print 'Add is :'+cast(@c as char)
End

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

create function <Function name>(@parameter)[Datatype]
Returns (Column Datatype)
As
Begin
Return (column Datatype)
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.

Create function sfunction(@x int)
Returns int
As
Begin
Return @x *@x*@x
End
Select dbo.sfunction (2)
Result

Table Value Function

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

Syntax

Create function <Fname>(@parameter)[Datatype]
Returns Table
As
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.