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
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.
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.
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] (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
|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 parameters||They 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 operatoins||Only Select & Insert can be used for inserting data in variable|
|Can't be used in WHERE , SELECT HAVING||Can be used in WHERE , SELECT HAVING|
|Have the ability to call functions||They cannot call SPs|
|Can handle Transaction Management||Unable to handle Transaction Management|
|My favourite These SPs have the ability to use Dynamic SQL||Doesn'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!