Home > DeveloperSection > Blogs > Stored Procedure in SQL Server

Stored Procedure in SQL Server


Database Database 
Ratings:
0 Comment(s)
 1893  View(s)
Rate this:

Stored Procedure in SQL Server

Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration and its data.

A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.

Types of Stored Procedure

Basically there are two types of stored procedure first is System Stored Procedure and second one is User Define Stored procedure.

System Stored Procedure:

In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures.

System stored procedure is those stored procedure which is available by the SQL server such as for renaming table name there is one stored procedure namely SP_RENAME , so such types of stored procedure are called system stored procedure.

User Define Stored Procedure:

User Define stored procedure are those procedure which are developed by user for obtaining particular goal of task. The user are restrict to create stored procedure in master database with prefix name sp of stored procedure, because sp prefix name is used for system stored procedure in master database.

Creating a User Define Stored Procedure

You create stored procedures in the SQL Server Management Studio (SSMS) using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.

Syntax for Stored Procedure

--SYNTAX DEMONSTRATION OF CREATING USER DEFINE STORED PROCEDURE

CREATE PROCEDURE <PROCEDURE_NAME>

(

  -- PARAMETER OF STORED PROCEDURE

)

AS

BEGIN

--PROCEDURE BODY

--WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE

END

GO

Example: CREATING STORED PROCEDURE

SET ANSI_NULLS ON -- that compares a value with a null return a 0(zero)

GO

SET QUOTED_IDENTIFIER ON -- double quotation mark is used as part of the SQL Server identifier(object name)

GO

 

-- creating procedure

 

CREATE PROCEDURE TEST_PROCEDURE

      -- Add the parameters for the stored procedure here

      @ID INT

AS

BEGIN

    -- SELECT statements for procedure here

      SELECT * FROM info where id = @ID

END

 

Execution Stored Procedure

EXEC TEST_PROCEDURE '1' -- calling stored procedure with passing parameter

Drop Stored Procedure

 

DROP PROC TEST_PROCEDURE -- we can use PROC in place of PROCEDURE keyword

Alter Stored Procedure

-- Modify the Procedure

 

ALTER PROC TEST_PROCEDURE

      -- Add the parameters for the stored procedure here

      @NAME VARCHAR(50)

AS

BEGIN

    -- SELECT statements for procedure here

      SELECT * FROM info where [name] = @NAME

END

For more information you can prefer this link…

http://www.mindstick.com/Beginner/79BE36EC-D4E6-4EF5-AE5D-C65FD7EDE2B9/SQL%20Server/Stored%20Procedure%20in%20SQL/


Don't want to miss updates? Please click the below button!

Follow MindStick