Difference Between Stored Procedure & Functions

Stored Procedure And Function In SQL With Examples is the topic for today . Hello future & present database experts let’s  discuss

1. What is Stored Procedure in SQL
2. Stored Procedure Syntax
3. How to run Stored Procedure in SQL
4. User-Defined Functions & SQL Functions.
5. Difference between Stored Procedures & Functions in SQL Server.

We will take SQL Server Stored Procedure Example & Function Example from our sample database.  I recommend you create this database. It will help you in a better understanding of this tutorial.

Let’s cut to chase now & learn SQL Stored Procedures first.

What is Stored Procedure in SQL

A stored procedure is a set of pre-compiled SQL statements that are used to perform a special task

what do I mean by pre-compiled SQL Statements? It means that your DBMS compiles & parse the SQL Statements after you create a stored procedure & is awaiting your execution call to execute the query.

Stored Procedures are very favourites of DBAs for reasons such as

1. They can accept input parameters
2. They can have output parameters as well & in case you don’t want to return anything. That is fine with SPs as well
3. They provide better security to data. You can allow users to execute the queries without letting them see the actual SQL Statements. Which can be of literally , hundreds of lines
4. They are super-fast. Reason is everything that is to be done with SQL Statement , such as its parsing , preparing query plan are done when a SP is created.
5. They come very handy with Dynamic SQL & to perform DDL queries.
6. Create a 100 line query & then create SQL SP on it. Now you can call those hundred lines of code with just one line & use it as many time as you desire.

Stored Procedures are heavily used together with Dynamic SQL . In future , BI TALKS BI plans to write a comprehensive guide on Dynamic SQL , I will recommend you to Subscribe US and stay updated with latest articles.

SQL Stored Procedure Syntax

Note this Syntax to create a Stored Procedure

CREATE PROCEDURE procedurename
 AS
 SQL Statement

What Is A Stored Procedure In SQL With Example

CREATE PROCEDURE HelloWorldprocedure
AS
PRINT 'Hello World'

A stored procedure is used to retrieve data, modify data, and delete data in database table. You don’t need to write a whole SQL command each time you want to insert, update or delete data in an SQL database.

EXAMPLE 2 FROM OUR SAMPLE DATABASE

CREATE PROCEDURE [dbo].[UpdatePerson]
@PersonID int,
@LastName nvarchar(50),
@FirstName nvarchar(50),
@HireDate datetime,
@EnrollmentDate datetime
AS
UPDATE Person SET LastName=@LastName,
FirstName=@FirstName,
HireDate=@HireDate,
EnrollmentDate=@EnrollmentDate
WHERE PersonID=@PersonID;

Input Parameter are given here , which means this SP will require you to input these in the same order & a record will be updated. This SP is already in our Sample database , so you can just view it from there or see the code here !

Let’s execute these SPs now.

How To Run Stored Procedure In SQL

Two SPs that we created . SP HelloWorldprocedure  doesn’t have any Input Parameter and our second DeletePerson have one Input Parameter . We will see how both of them are executed.

EXEC HelloWorldprocedure

Here’s how you will execute SP with parameters

EXEC [dbo].[UpdatePerson]
@PersonID = 1,
@LastName = N'Ali',
@FirstName = N'Awais',
@HireDate = N'2002-08-06 00:00:00.000',
@EnrollmentDate = N'2002-09-01 00:00:00.000'

This will update the PersonID 1 with my name . See screenshots Interesting Stuff! Right?

So you now have SQL Server Stored Procedure

User-Defined Functions & SQL Functions

Functions are widely used in computer world. If you’re a familiar with other programming language you must have heard about them . Similarly , SQL Functions do more or less same job .

There are two types of functions in SQL

SQL Built-In Functions

These are built-in functions to make it easy for you to do the jobs a lot quicker. CONCAT , CAST are two of many built-in functions of SQL Server . You can just use them directly in any SQL Statement.  We are summing up a tutorial on this . We will give you Syntax and examples of SQL functions so stay Tuned.

User-Defined Functions

Also knows as UDFs is also an SQL statement that can be use to return one single value or even a table. However , unlike SPs they have to return a value. Further UDFs have two types

1. Scalar Valued Functions
2. Table Valued Functions

Scalar Valued Functions Syntax & Example

Returns a single value as a result . See the Syntax & then examples

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
 -- Add the parameters for the function here
 <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
 -- Declare the return variable here
 DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int> -- Add the T-SQL statements to compute the return value here
 SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1> -- Return the result of the function
 RETURN <@ResultVar, sysname, @Result>END
GO

Let’s create a Scalar Function which will convert SQL Datatime format to int

CREATE FUNCTION [dbo].[DateTimetoInt] (@Datetime Datetime )
returns BIGINT
BEGIN
DECLARE @DATE AS BIGINT
SELECT @DATE =
CAST(
CAST(DATEPART(YEAR,@Datetime) AS VARCHAR) + -- get the year
right('0' + CAST (DATEPART(MONTH,@Datetime) AS VARCHAR),2) + --get the month and zero pad
right('0' + CAST (DATEPART(DAY,@Datetime) AS VARCHAR),2) +
right('0' + CAST (DATEPART(HOUR,@Datetime) AS VARCHAR),2)+
right('0' + CAST (DATEPART(MINUTE,@Datetime) AS VARCHAR),2) +
right('0' + CAST (DATEPART(SECOND,@Datetime) AS VARCHAR),2) --get the day and zero pad
as BIGINT)
RETURN @DATE
END;

We can call this function in any Select query as column or simply with Select only. Here , we will use this function to transform the SQL datetime format column to SQL Int format

SELECT [dbo].[DateTimetoInt] ( '2005-09-01 00:00:00.000')as DateTimeToInt

Table Valued Functions Syntax & Example

Returns a table as a result.See the Syntax & then examples

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
 (
 -- Add the parameters for the function here
 <@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
 <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
 )
 RETURNS TABLE
 AS
 RETURN
 (
 -- Add the SELECT statement with parameter references here
 SELECT 0
 )
 GO

We will create a function which will give us complete information about Person , when I will pass that PersonID to it.

 CREATE FUNCTION dbo.GetNamesFromID
 ( -- Add the parameters for the function here
 @PersonID int )
 RETURNS TABLE
 AS RETURN
 ( -- Add the SELECT statement with parameter references here
 SELECT * FROM [School].[dbo].[Person] AS Student
 WHERE PersonID=@PersonID )

See the result

 SELECT * FROM [dbo].GetNamesFromID

Practice SQL Queries : SQL Queries & SQL Commands

Difference between Stored Procedures & Functions in SQL

I love when people ask differences between Stored Procedures & Functions because that is truly when you will be able to understand them & know what to use in a situation.

Both of these can be confusing sometimes , because they look very same in the functionality . My rule for such situation is  If It looks Easy , It’s Fishy ! I mean why would smart people create two things which are very same in nature. The answer is they only look . In real they are as different as Moon from Sun.

Following are few differences

Ahhhhh! Such a long article but i hope you enjoy this & get to learn some useful concepts. There is a lot we can discuss on Stored Procedure And Function SQLq but this should be enough for now. As of now BI Talks BI is compiling Optimisation Practices & Dynamic SQL series. Hopefully , you will see much more about topics there.

You can Subscribe Us for more updates ,follow us on twitter , facebook , Quora & Reddit. Comment below if you have anything to discuss or share!

See you with next article very soon!

Happy Learning!
Cheers!