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
- What is Stored Procedure in SQL
- Stored Procedure Syntax
- How to run Stored Procedure in SQL
- User-Defined Functions & SQL Functions.
- 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
- They can accept input parameters
- They can have output parameters as well & in case you don’t want to return anything. That is fine with SPs as well
- 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
- 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.
- They come very handy with Dynamic SQL & to perform DDL queries.
- 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
- Scalar Valued Functions
- 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!
Thank you for this article, it helps a lot 🙂
Glad to be of help!