Stored Procedures in SQL server

In this post I  explained about what is stored procedure and what are the advantages and disadvantages of stored procedures in sql server

What is stored procedure:-

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

First create user table  :-

CREATE TABLE [dbo].[tblUSER](

      [USR_ID] [int] IDENTITY(1,1) NOT NULL,

      [USR_NAME] [nvarchar](50) NULL,

      [USR_LOGIN] [varchar](100) NOT NULL,

      [USR_PASSWORD] [varchar](25) NOT NULL,

      [DESIGNATION] [varchar](50) NULL,

      [USR_ROLEID] [int] NULL,

      [CREATED_DATE] [datetime] NOT NULL,

      [EmailId] [varchar](max) NULL,

      [USR_STATUS] [varchar](50) NOT NULL,

      

 )

1. Creating a Stored Procedure to insert user details:-

/****** Object:  StoredProcedure [dbo].[sp_CreateUser]    Script Date: 05/16/2015 18:24:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_CreateUser]

       @USR_NAME varchar(MAX),

       @USR_LOGIN varchar(100),

       @USR_PASSWORD varchar(25),

       @DESIGNATION VARCHAR(500),

       @USR_ROLEID int,

       @EmailId varchar(MAX)

AS

BEGIN

     

      SET NOCOUNT ON;

   INSERT INTO tblUSER(USR_NAME ,

                       USR_LOGIN,

                       USR_PASSWORD,

                       USR_ROLEID,

                       CREATED_DATE ,

                       EmailId,

                       USR_STATUS ,

                       DESIGNATION)

          VALUES (@USR_NAME ,

                  @USR_LOGIN ,

                  @USR_PASSWORD ,

                  @USR_ROLEID ,

                  getdate() ,

                  @EmailId ,

                  'ACTIVE',

                  @DESIGNATION)

END

2. Creating a stored procedure to select user details:-

Create PROCEDURE [dbo].[sp_SelectUser]

AS

BEGIN

     

   Select USR_NAME ,

              USR_LOGIN,

              USR_PASSWORD,

              USR_ROLEID,

              CREATED_DATE ,

              EmailId,

              DESIGNATION

   from tblUSER

END

Advantages of using stored procedures

1. Stored Procedure is precomplied code so its allows faster execution.If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

2. Stored procedure allows modular programming.  You can create the procedure once, store it in the database, and call it any number of times in your program.

3. Stored procedures provide better security to your data Users can be granted permission to execute a  stored procedure even if they do not have permission to execute the procedure's statements                  directly.

4.  Stored Procedure can reduce network traffic. An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

In SQL we are having different types of stored procedures are there

a)    System Stored Procedures
b)    User Defined Stored procedures
c)    Extended Stored Procedures

System Stored Procedures:

System stored procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be used to perform variety of tasks to support sql server functions for external application calls in the system tables

Ex:

exec sp_helptext [StoredProcedure_Name]

User Defined Stored Procedures:

User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database

Extended Stored Procedures:

Extended stored procedures are the procedures that call functions from DLL files. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.

In the above article I try to explain system defined as well as user defined Stored Procedures in SQl with example. I hope its useful to you. Please post your feedback, question, or comments about this article.

System defined and user defined Functions in SQL server

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).

Types of Functions:- 

There are two types of Functions in sql server. 

1. System Defined Function:-

These functions are built in functions in Sql Server for different purpose. We have two types of system defined function in Sql Server

1.a  Scalar Function:-
Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.

Sl.No.	Scalar Function	Description
1	convert(int, 20.89)	This will convert the given float value to integer means 20
2	ltrim(' sql')	This will remove the spaces from left hand side of ‘sql’ string.
3	lower('SQL')	This will returns lower case of given string means 'sql'
4	upper('sql')	This will returns upper case of given string means ‘SQL’
5	round(56.27128,2)	This will round off the given number to 2 places of decimal means 56.27
6	reverse('hhhaa')	This will reverse the given string and retruns  ‘aahh’
7	datediff(day,'2015-05-10','2015-05-16')	This will returns difference of two dates in day. here return value is 6
8	Concat(‘Ram’,’pal’)	This will concat first and second values and return Rampal
9	CAST(GETDATE() as varchar)	This will cast date object as string and return ‘May 16 2015  3:57PM’

1.b Aggregate Function:-

Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.

SlNo.	Aggregate Function	Description
1	MAX()	This returns maximum value from a collection of values.
2	MIN()	This returns minimum value from a
3	AVG()	This returns average of all values in a collection.
4	COUNT(*)	This returns no of counts from a collection of values.

2. User Defined Function

These functions are created by user in system database or in user defined database. We three types of user defined functions.

2a. Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

Create a Database table [EmployeeMaster]

USE [Company]

GO

CREATE TABLE [dbo].[EmployeeMaster]

 (

      [EmpID] [int] IDENTITY(1,1) NOT NULL,

      [FirstName] [nvarchar](50) NULL,

      [LastName] [nvarchar](50) NULL,

      [Salary] [nvarchar](50) NULL,

      [IsActive] [bit] NULL

 )

GO

Insert Some Records

USE [Company]

GO

/****** Object:  Table [dbo].[EmployeeMaster]Script Date: 05/16/2015 16:21:22 ******/

SET IDENTITY_INSERT [dbo].[EmployeeMaster] ON

INSERT [dbo].[EmployeeMaster] ([EmpID], [FirstName], [LastName], [Salary], [IsActive])

 VALUES (1, N'Suraj', N'Pal', N'2000', 1)

INSERT [dbo].[EmployeeMaster] ([EmpID], [FirstName], [LastName], [Salary], [IsActive])

 VALUES (2, N'Rampal', N'Singh', N'2100', 1)

INSERT [dbo].[EmployeeMaster] ([EmpID], [FirstName], [LastName], [Salary], [IsActive])

 VALUES (3, N'Jagram', N'saini', N'4000', 1)

INSERT [dbo].[EmployeeMaster] ([EmpID], [FirstName], [LastName], [Salary], [IsActive])

 VALUES (4, N'Neetu', N'Singh', N'3000', 1)

SET IDENTITY_INSERT [dbo].[EmployeeMaster] OFF

Now Recors in table are:-

Now Create a function in database using code below:-

Create function fnGetFullName

(

 @FirstName varchar(50),

 @LastName varchar(50)

)

returns varchar(101)

As

Begin return (Select @FirstName + ' '+ @LastName);

end

And Now call above function in select query it will concat first and last name and return full name as result.

2.b Inline Table-Valued Function

User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

Create a Inline function:-

Create function fnGetEmpTable()

returns Table

As

 return (Select * from dbo.EmployeeMaster)

Now call the above function in Select query it will return table :-

2.c  Multi-Statement Table-Valued Function

User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

Now create a multi statement function:-

Create function fnGetMultiStateMnt_Employee()

returns @Emp Table

(

EmpID int,

FirstName varchar(50),

Salary int

)

As

begin

 Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;

--Now update salary of first employee

 update @Emp set Salary=23000 where EmpID=1;

--It will update only in @Emp table not in Original Employee table

return

end

And now call the above multi statement function:-

In the above article I try to explain system defined as well as user defined functions in SQl. I hope its useful to you. Please post your feedback, question, or comments about this article.