Stored Procedure And Function SQL Example

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

SQL Stored Procedure 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?

Stored Procedure
Before SP
Stored Procedure
After SP

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

Stored Procedure
Scalar Function

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] (1)

 

Difference between Stored Procedures & Functions in SQL Examples

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

Stored ProceduresFunctions
Returning data is not necessary. You have a choice.Returning data is necessary. You do not have a choice.
Scalar/Table both types of return data
They can have input & output parametersThey Only have input parameters
You can do TRY CATCH in SPs which makes it easier for use in Dynamic SQL.You cannot do TRY CATCH at all.
You can use DDL , DML operatoinsOnly Select & Insert can be used for inserting data in variable
Can't be used in WHERE , SELECT HAVINGCan be used in WHERE , SELECT HAVING
Have the ability to call functionsThey cannot call SPs
Can handle Transaction ManagementUnable to handle Transaction Management
My favourite These SPs have the ability to use Dynamic SQLDoesn't handle Dynamic SQL

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 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!

 

Advertisements

Author: Awais

Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows ! Happy Reading and Learning!

Leave a Reply