Skip to main content

What is Stored Procedure in MS SQL?

A Stored Procedure is a group of sql statements that has been created and stored in the database.

Advantages

  • It is actually stored in the database data dictionary and Pre compiled one. We can call any number of times from our program. 
  • Reduce the need to encode the logic elsewhere in client programs.
  • Stored procedure will reduce network traffic and increase the performance. Consider if we have repeat statements to call then how many times we have to call Database better a single stored procedure can use for all the statements and a single call is enough instead of multiple calls.
  • A single procedure can be used over the network by several clients using different input data. 
  • If we modify stored procedure all the clients will get the updated stored procedure.
  • Security - Stored Procedure can be granted access rights to the database that users who execute those procedures do not directly have.
  • Stored Procedure can be used to protect against SQL Injection attacks.
  • Inline Stored Procedure, we can call a stored procedure from other stored procedure
  • We can call functions from stored procedures,  the main difference of stored procedure and function is functions must return a value (using the RETURN keyword), but for stored procedures this is not compulsory.
Disadvantages
  • Switching to another vendor's database most likely requires rewriting any existing stored procedures.
  • Debugging stored procedures is often not as good as for other programming languages.
  • Changes to stored procedures are more difficult to keep track of within a system.
  • Differences in procedures can be more difficult to merge and track correctly.

Types
  • User Defined Stored procedure: This created by users and stored in the current database, we will discuss more about this.
  • System Stored Procedure: This manage SQL Server through administrative tasks have names prefixed with sp_.
  • Temporary Stored procedures:  This stored in the tempdb databases have names prefixed with the # symbol. These procedures are automatically dropped when the connection  terminates between client and server.
  • Remote Stored Procedures: This created and stored in remote servers can be accessed from various servers, provided the users have the appropriate permission
  • Extended Stored Procedures: This call functions from DLL files have the prefix xp_.
  • CLR Stored Procedure: CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#.

User Defined Stored Procedure
To Create Stored Procedure from Object Explorer, Databases --> Programmability --> Stored Procedures right click and then click New Stored Procedure.  This will give a structure of stored procedure.
To Create Stored Procedure from Query Editor, from file menu click New Query and select database where the stored procedure have to save.  Here we have to type from the empty Query Editor.
We can start with a simple stored Procedure before you create a stored procedure you need to know what your end result is, whether you are selecting data, inserting data, delete data, or update data. Here I am giving a simple select statement returns some data. We can consider an Employee table.
Simple select statements
Select * from Employee

Here I am creating a simple stored procedure with above select statements

CREATE PROC spGetEmployeeDetails
AS
BEGIN
 Select * from Employee
END
GO

We can use  CREATE PROCEDURE or CREATE PROC for create stored procedure. Execute above SP (or simply press F5), then will save our stored procedure into database if there is no errors. If error is there then we have to rectify error then only it will save. If stored procedure executed once then if we try to execute this again then we get an error message

"Msg 2714, Level 16, State 3, Procedure spGetEmployeeDetails, Line 1
There is already an object named 'spGetEmployeeDetails' in the database." Here we have to use ALTER instead of CREATE. Means we can save one stored procedure name in one time only If the name existing then we can modify using ALTER.

ALTER PROC spGetEmployeeDetails
AS
BEGIN
 Select * from Employee
END
GO

To call the procedure to return the contents from the table specified, the code would be:

EXEC spGetEmployeeDetails
--or just simply
spGetEmployeeDetails (Press F5 or Execute) then you will get results. we cannot use the keyword "GO" in the stored procedure.  Once the SQL Server compiler sees "GO" it assumes it is the end of the batch. we created stored procedure without any parameter now we can see with parameters.
CREATE PROCEDURE spGetEmployeeDetails @Name nvarchar(50)
AS
BEGIN
   Select * from Employee WHERE Name = @Name
END
GO

For call the stored procedure,

EXEC spGetEmployeeDetails @Name = 'Jom George'
Or  EXEC spGetEmployeeDetails 'Jom George' we can execute both ways.


Here we get the details of Jom George. For this if we not supplied parameter then

Msg 201, Level 16, State 4, Procedure uspGetAddress, Line 0

Procedure or function 'spGetEmployeeDetails' expects parameter '@Name', which was not supplied.


So we must pass parameter here. if we give

 CREATE PROCEDURE spGetEmployeeDetails @Name nvarchar(50) = NULL then the parameter is set default as NULL, this all depend on situations we can use. Now if we not pass the parameter then also stored procedure will execute but we have to give ISNULL in conditions.

EXEC spGetEmployeeDetails @Name = 'Jom George' or just EXEC spGetEmployeeDetails here both execution will work because default value assigned to parameter.  Now we can see multiple parameter passing in the stored procedure.

CREATE PROCEDURE spGetEmployeeDetails @Name nvarchar(50)=NULL, @EmpID int

for multiple parameters we have to separate using ",". Now we have to pass both parameters because @EmpID is doesn't have any default value and parameter position is second.
EXEC spGetEmployeeDetails 5, if we give like this then procedure will consider 5 as first parameter and EmpID not passed, so will get an error like below.

Msg 201, Level 16, State 4, Procedure spGetEmployeeDetails, Line 0
Procedure or function 'spGetEmployeeDetails' expects parameter '@EmpID', which was not supplied.

Here we can do either pass two parameter or with parameter name we can pass.

spGetEmployeeDetails NULL, 5 or spGetEmployeeDetails 'Jom', 5 or
spGetEmployeeDetails  @EmpID=5

See the 3rd way it's possible because other parameter having default value. Also we can give both parameters default value as null. You have to work all with situations.

Now we can see OUTPUT parameters

CREATE PROCEDURE spGetEmployeeDetails
   @EmpID INT,
   @ManagerID INT OUTPUT
AS
BEGIN
   SELECT @ManagerID = ManagerID 
   FROM Employee  WHERE EmployeeID = @EmpID
END

Here we passed @ManagerID as output parameter.

Note 1: sp_help Stored_Procedure_Name: Used to view the information about the stored procedure like parameter names, their datatypes etc. sp_help can be used with any database object, like Tables, Views, SPs, Triggers etc. Alternatively, you can also press ALT+F1, when the name of the object is highlighted.

Note 2: sp_helptext Stored_Procedure_Name : Used to view the Text of the stored procedure. For example, when we use sp_helptext spGetEmployeeDetails, we will get the text of this stored procedure.

Different datatypes we can pass as parameters integer, decimal, character, xml etc now we can see how xml parameter works..

CREATE PROCEDURE spGetEmployeeDetails
@List XML
AS
BEGIN
DECLARE @idoc INT
      DECLARE @data nvarchar(100)

      EXEC sp_xml_preparedocument @idoc OUTPUT, @List

    SELECT EmpID,
    firstname,
    lastname,
    company,
    email,
    DateofJoin INTO #Employee FROM  OPENXML(@idoc,

    '/NewDataSet/EmployeeDate', 6)

    WITH

    (EmpID int ,
    firstname nvarchar(50),
    lastname  nvarchar(50),
    company    nvarchar(max),
    email nvarchar(100),
    DateofJoin datetime
    )
    end

GO

See here @List I am passing as XML parameter. see here sp_xml_preparedocument is a system stored procedure for XML parameter. Also you can see how we can call inline stored procedure here (stored procedure call inside the other stored procedure). Here its a system stored procedure same like we can call user defined stored procedure, function etc. While executing sp_xml_preparedocument we get the data and using OPENXML function we can get the data. You can also see here I used Temporary table #Employee. Temporary table we can use for the temporary data storage.

Select * into #Employee from  Employee This will insert all the data in the Employee table to temporary table. Then we can use this table for any operation within this execution. See in the above XML stored procedure example I got the data in to #Employee table.


CREATE PROCEDURE spGetEmployeeDetails
@List XML
AS
BEGIN
DECLARE @idoc INT
      DECLARE @data nvarchar(100)

      EXEC sp_xml_preparedocument @idoc OUTPUT, @List

    SELECT ROW_NUMBER() OVER(ORDER BY EmpID DESC) AS RowID,
    EmpID,
    firstname,
    lastname,
    company,
    email,
    DateofJoin INTO #Employee FROM  OPENXML(@idoc,

    '/NewDataSet/EmployeeDate', 6)

    WITH

    (EmpID int ,
    firstname nvarchar(50),
    lastname  nvarchar(50),
    company    nvarchar(max),
    email nvarchar(100),
    DateofJoin datetime
    )
-- Above you can see Row ID, we are using ROW_NUMBER() Function using for get row numbers.
-- This will simply insert into Employee table

Insert into Employee (EmpID,first_name,last_name,company,email,DateofJoin)
SELECT EmpID,
    firstname,
    lastname,
    company,
    email,
    DateofJoin FROM #Employee

-- See I want to update data 1 By One Either I can use Curser or a simple While loop.
--here I am going to use while loop.

      Declare @Count int, @ID int

       Select @Count = Count(*) from #Employee
       set @ID=1

       While @Count>0
       Begin
          Update E  Set E.email=E1.email From Employee E
          Inner Join #Employee E1 on E.EmpID=E1.EmpID
          Where E1.RowID=@ID

          SET @Count=@Count-1
          Set @ID=@ID+1
       End


END
GO

See here I used while loop and updated one by one row.  If we want to delete also we can do this way. Also multiple statements we can use inside the stored procedure. All are depend on situations.
You can create table as temporary variables too
Declare @Employee Table
(
    RowID int identity(1,1),
       EmpID int ,
    firstname nvarchar(50),
    lastname  nvarchar(50),
    company    nvarchar(max),
    email nvarchar(100),
    DateofJoin datetime
)

Instead of #Employee you can use @Employee. Then you can insert into this temp table and use appropriate.

 Now you can test yourself with more queries. This is only for starting. a lot left in stored procedure you can do any logic here.  If you have any doubt in stored procedure please give a comment here.  


Comments

Post a Comment

Popular posts from this blog

OOP Concept with Real Time Examples

A design philosophy is OOP. Object Oriented Programming is what it stands for. In contrast to outdated procedural programming languages, object-oriented programming (OOP) employs a separate set of programming languages. In OOP, everything is categorised as self-sustaining "objects". As a result, you achieve re-usability using the four core concepts of object-oriented programming. Programmes are organised around objects and data rather than action and logic in the object-oriented programming (OOP) paradigm.    Let's use your "Leg" as an example to grasp the object orientation clearly. The class "Leg" is one. Left and right legs are objects of type Leg on your body. A series of electrical impulses supplied through your body parts (through an interface) are what manage or control their primary functions. As a result, the body part serves as an interface between your body and your legs. The Leg is a well-designed class. The attributes of the Leg are m...
6 comments
Read more

What is difference between abstract class and interface and when should we use interface and abstract class?

Although you can generate derivatives from this, you cannot create an object of the abstract class. Either abstract or non-abstract methods can be found in an abstract class. There is no implementation for abstract members in the abstract class; nevertheless, a derived class must supply one. Both abstract and non-abstract members can be found in an abstract class. The members of an interface, however, must all override the members of its derived class because all interface elements are implicitly abstract in nature. Similar to defining an interface, declaring an abstract class includes all of its abstract members. Specifically, we can say that an interface is a class that has all abstract members. Classes can only descend from one base class, therefore if you wish to use abstract classes to give a bunch of classes polymorphism, they must all all descend from that base class. Members that have already been put into practise may also be offered by abstract classes. With an abstract class...
Post a Comment
Read more

Windows Application Development - Dotnet Environment Basic understandings

The development life cycle for creating Windows desktop applications using the .NET framework typically involves several stages. Here's an overview of the typical life cycle: Requirement Analysis: Gather and analyze the requirements for the Windows application. Understand the business needs, user expectations, features, and functionalities that the application should have. Design: Create a design for your application's user interface (UI) and overall architecture. Decide on the layout, controls, navigation flow, and other visual aspects. Plan the data storage mechanisms, database schema, and integration with other systems if necessary. Development: Begin coding the application using the .NET framework. Use programming languages like C# or VB.NET. Create classes, forms, controls, and implement the business logic. You'll work on creating the UI, handling user interactions, data processing, and any required integrations. Testing: Thoroughly test the applicatio...
Post a Comment
Read more