Tags: Database , Stored Procedure

What is Stored Procedure?
Stored Procedure is nothing but the Program of database. In other words we can say its user defined function or method of database. That contains mixed database query like Insert, Select, Update, Delete, Join, Cursor, etc... in one file.

Why Stored Procedure?
Stored Procedure is very useful in case of complex project structure. Where you need to perform more than one operation on database. Write all you database queries in one SQL file, pass required parameter in it and rest will be on Stored Procedure.
Its not recommended to use Stored Procedure in case of one or two query execution.

Examples
Lets have hands on basic example of Stored Procedure. For better understanding and just to server basic idea. I'll use one query in my stored procedure however I won't recommend you to use it for one query.

1. Select all data of one organization (using organization id) and create new copy for new organization(using new organization id)

USE [NAME_OF_DATABASE]
GO
/*
 * Microsoft SQL Server
 * Stored Procedure : copyData.sql
 * Date of Creation : 24th July, 2013  
 * @author: Vicky Thakor
**/
/*
 * This option specifies the setting for ANSI NULL comparisons. 
 * When this is on, any query that compares a value with a null returns a 0. 
 * When off, any query that compares a value with a null returns a null value.
 **/
SET ANSI_NULLS ON
GO
/*
 * This options specifies the setting for usage of double quotation. 
 * When this is on, double quotation mark is used as part of the SQL Server identifier (object name). 
 * This can be useful in situations in which identifiers are also SQL Server reserved words.
 **/
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[copyData]
  -- Add the parameters for the stored procedure here
  @NewOrganizationID int,
  @OldOrganizationID int
AS
BEGIN/* PROCEDURE BEGIN */
 BEGIN TRANSACTION
        /* Stops the message that shows the count of the number of rows affected 
 * by a Transact-SQL statement or stored procedure from being returned as part of the result set. 
 * example : (1 row(s) affected)
 * Syntax : SET NOCOUNT { ON | OFF } 
 **/ 
        SET NOCOUNT ON;
        PRINT 'Copy data from Organization '+CAST(@OldOrganizationID AS VARCHAR(10))+' to '+CAST(@NewOrganizationID AS VARCHAR(10))
        /* Start: Copy rows */
        /* 
         * Below query find all rows where condition matches and insert new rows with new organizationID.
         * It doesn't matter how many rows are there.
         * To limit data you can use TOP n keyword after SELECT statement.
         */
        INSERT INTO [dbo].[TABLE_NAME]
               ([column_1] ,[column_2] ,[column_3] ,[ORGANIZATION_ID])
        SELECT  [column_1] ,[column_2] ,[column_3] ,@NewOrganizationID
        FROM TABLE_NAME
        WHERE ORGANIZATION_ID = @OldOrganizationID
        /* End: Copy rows */
        COMMIT TRANSACTION
END/* PROCEDURE END */
GO

2. Example of IF and WHILE for Microsoft SQL Server

USE [NAME_OF_DATABASE]
GO
/*
 * Microsoft SQL Server
 * Stored Procedure : Example2.sql
 * Date of Creation : 24th July, 2013  
 * @author: Vicky Thakor
**/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Example2]
  -- Add the parameters for the stored procedure here
  /* bit used for true or false in form of 1 and 0 */
  @PrintIFFlag bit,
  @LOOP_TO int
AS
BEGIN/* PROCEDURE BEGIN */
 BEGIN TRANSACTION
        /* Stops the message that shows the count of the number of rows affected 
 * by a Transact-SQL statement or stored procedure from being returned as part of the result set. 
 * example : (1 row(s) affected)
 * Syntax : SET NOCOUNT { ON | OFF } 
 **/ 
        SET NOCOUNT ON;
        DECLARE INDEX_COUNT = 0;

        IF @PrintIFFlag = 1
 BEGIN
  PRINT 'YOU ARE IN IF STATEMENT'
 END

        WHILE @INDEX_COUNT != @LOOP_TO
        BEGIN
              PRINT 'INDEX:'+CAST(@INDEX_COUNT AS VARCHAR(10))
              SET @INDEX_COUNT = INDEX_COUNT + 1;
        END         
        COMMIT TRANSACTION
END/* PROCEDURE END */
GO
-- EXEC Example2 1,100

How to execute Stored Procedure?
This Stored Procedure created for Microsoft SQL Server. Follow the below steps for complete execution.

1. Execute Stored Procedure so for the first time it creates copy of stored procedure in database. For better understanding use Microsoft SQL Server Studio.
2. After First execution change CREATE to ALTER because when you update something in your Stored Procedure you need to just alter the existing Stored Procedure. Database won't allow you to create Stored Procedure with same name.
3. Fire EXEC NAME_OF_SP parameters. Check last line in example2

You have to be very careful while executing the stored procedure. As Stored Procedure perform lots of operation in database. You are recommended to follow some rules.

• Before you create Stored Procedure on client's database test it on your local database.
• While testing Stored Procedure Print required information using PRINT command. Like inserting, deleting, etc... 
• If you are creating copy of information using some ID print previous ID and new ID. It'll help you to  delete the data from database. Stored Procedure will create garbage data on your database if you are not deleting test data.

Tags: Database , Stored Procedure